Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003

    help with sales database

    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
    jobNo----------------job number
    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
    commission earned
    commission paid
    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 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?

    Again, thanks for your help

  2. #2
    Join Date
    Sep 2002

    Re: help with sales database

    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.

  3. #3
    Join Date
    Feb 2003
    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?

  4. #4
    Join Date
    Sep 2002
    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.

    Qute how/if this works in Access, I do not know.

  5. #5
    Join Date
    Feb 2003
    I'll post the question in the Access forum. thanks!

  6. #6
    Join Date
    Mar 2003

    Access for the sales database

    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....


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts