Hi, I'm working on a database project that tracks sales and production records for an advertising agency. The database would be online and accessed through active server pages, but I want to make sure my database is anamoly free. These are the fields that I need:
salesRep-------------name of sales rep
city-----------------city of job
region---------------region of job East, West, Int.
jobType--------------type of job
client---------------client company's name
clientProducer-------client producer's name
inquiryComments------comments made by sales rep
Producer-------------agency's producer's name
bidAmount------------our bid amount for the job
jobAmount------------the actual job amount
jobStatus------------did we get the job
firstInvoiceNo-------1st invoice for job
firstInvoiceDate-----date of invoice
firstInvoiceAmount---amount of invoice
comments-------------comments by producers
lastUpdated----------date the record was last updated
A record is entered by a sales rep first with his/her name, city, region, client, clientProducer and jobType. LATER ON, a producer accesses the same record and fills in the rest.
Ok, I know that there are no repeating groups so I think this is already in first normal form. If I create a new field called recordID and then use this as a primary key, I can avoid composite keys. Then there would be no partial dependecies and it would be in 2nd normal form (Is this right??). As for other dependencies, I'll group city with the region (such as LA is in West and NYC is in East) in a seperate table called cities. ClientProducers and Clients probably go together too. I now have a 'records' table holding the main data with relations to 'cities' and 'clients' tables. 3 tables...is this all or is there more? You can tell that I'm a newbie so any help would be greatly appreciated.
Also, as I mentioned, people will have to access records more than once so updates are definite. I guess transactions are the way to go right? Is that my only option?
Is there a maximum of 2 invoices per job? Never 3? If so, that's OK.
Otherwise break out the invoice details into a separate table.
Avoiding composite keys by creating a surrogate key (recordID) is fine, but you should consider whether you should not also have a unique constraint for the natural key. Otherwise, how would you know if someone accidentally entered the same job twice - until the client objected to being charged twice?
"Transactions are the way to go?" - not sure what you mean by this, but yes, you will use transactions to control updates etc. They become important when a process involves more than one update/insert/delete. For example, if a process transfers money from account A to account B like this:
UPDATE account SET balance = balance - x WHERE account_no='A';
UPDATE account SET balance = balance + x WHERE account_no='B';
then it is crucial to put these 2 updates in a single transaction, so that you are guaranteed to update both tables or neither table (e.g. if an error occurs). You must not allow one update to success and the other to fail, otherwise you will have a corrupt database.
Thanks for the reply. I can see why transactions are used now, but if my primary table is the only table being updated, then is it still necessary to use transactions? What about when two people try to update the same record? By the way, I'm currently using Access and there will be about 15 people using this database online. The average number of records per year is about 10,000. Can Access cut it or do I have to get get something better?
You may want to put this question to the Access forum. It sounds like Access could just about handle it, but I'm not sure. Also, there may be some Access concept of transactions that is different to what I'm thinking of. In Oracle, you can't NOT have transactions: a transaction begins automatically as soon as you do your first insert/update/delete and ends when you COMMIT or ROLLBACK, so the issue of whether to use transactions or not just doesn't arise.
The issue with multiple users is locking: if 2 users select the same record onto their screen and make changes, then both save those changes, you don't want the second save to obliterate the changes made by the first - known as a "lost update". There are 2 ways around this, known as "optimistic" locking and "pessimistic" locking:
Optimistic - assumes the clash is unlikely. Both users can make changes to their copy of the record on the screen. When a user saves changes, the record on the screen (before any changes were made) is compared with the record in the database. If it is different, then that means someone else has changed it since it was selected, and this attempt to save is rejected. The user must then re-select the record (with the changes made by the other user) and make his own changes again.
Pessimistic - assumes clashes are likely. When the first user starts to change the record on the screen, a lock is taken on the record. The second user is then unable to make any changes (e.g. gets told "record is locked by another user") until the first user has saved his changes, which releases the lock.
I've done quite a few databases with Oracle and with Access. First you also cannot have no transactions with Access. Access also handles every insert/update/delete as a transaction. But it does it internally. You don't have the chance to control the transactions.
Second: I always recommend not to use Access for more than the personal record collection at home.
You are looking for 15 users and about 10 K datasets a year. That's not much, but still I'd choose a "grown-up" database to handle it.
Applications like the one you're about to design, tend to grow in complexity (like: Now I want to access all sales data for a region but according to, I don't know what, and so on)
You'd soon find out, that the first 90% of the work have been easy with access, but to do the rest will be very very tedious and might make you throw away much of the work, done before.
It's just a recommendation, but....
As for the design, I should think it looks ok, with the hints you already got. I would also store the invoices in separate tables.
One thing which gives you a feeling, as how good your design is:
Try to think about the queries, your users will probably want to do, and find out, how hard or easy they would be to answer....