Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Question Is this a good database for a store?

    Hi, I am new to the whole database and normalizing thing and I am working on an assignment. I was wondering if someone could take a look at my tables and let me know if I am missing fields, records, more tables, or have errors or if my primary key is wrong
    I also wanted to know if this database is in true 3NF, it looks good to me, but being new to it I might be missing something

    Customer Table
    customerID (Primary Key)
    customerFirstName
    customerLastName
    customerAddress
    zipCode

    Zip Code Table
    zipCode (Primary Key)
    city
    state

    Customer Telephone Number Table
    customerID (Primary Key)
    custTelephoneNumber (Primary Key)
    custTelephoneNumberDescription

    Product Table
    productID (Primary Key)
    productName
    productPrice
    productQuantityAvailable
    productDescription

    Product Re-Order Table
    productID (Primary Key)
    productQuantityAvailable (Primary Key)
    reorderLevel
    reorderQuantity

    Supplier Table
    supplierID (Primary Key)
    supplierName
    supplierAddress
    zipCode

    Supplier Telephone Number Table
    supplierID (Primary Key)
    suppTelephoneNumber (Primary Key)
    suppTelephoneNumberDescription

    Employee Table
    employeeID (Primary Key)
    employeeFirstName
    employeeLastName
    employeeAddress
    zipCode

    Employee Telephone Number Table
    employeeID (Primary Key)
    empTelephoneNumber (Primary Key)
    empTelephoneNumberDescription

    I put separate telephone number tables in, because my professor said that if a customer, etc. has more than one telephone number (work, home, cell) i couldn't list all of them in the customer table.

    I have three more tables that I am trying to figure out, a purchase table, a payment table, and a payment method table
    but I am getting a little confused trying to get them together. I am not sure if they are correct. The purchase table has so much information in it.
    And what if a customer buys more than one product, how do I fit that into the purchase table?
    I can't put more than one productID, so I am confused on that.
    I am trying to work out the payment table and I am having trouble with it. Like what if the customer pays with half cash and half credit card?
    any input would be very helpful

    Purchase Table
    purchaseID (Primary Key)
    customerID
    employeeID
    productID
    paymentID
    datePurchased
    quantityPurchased

    Payment Method Table
    paymentMethodID (Primary Key)
    paymentMethodDescription

    Payment Table
    paymentID (Primary Key)
    customerID
    purchaseID
    paymentMethodID
    amount

    Any help would be greatly appreciated
    Thanks a lot,
    Brian

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Re: Is this a good database for a store?


    I put separate telephone number tables in, because my professor said that if a customer, etc. has more than one telephone number (work, home, cell) i couldn't list all of them in the customer table.


    Telephone-numbers are often a problem and there's no easy answer to them. Many systems only store two or three, using separate columns. But inevitably some customer comes along who's sprouted cell-phones from every extremity and demands to be called on each one.


    And what if a customer buys more than one product, how do I fit that into the purchase table?
    I can't put more than one productID, so I am confused on that.
    I am trying to work out the payment table and I am having trouble with it. Like what if the customer pays with half cash and half credit card?
    any input would be very helpful


    Usually a "purchase" is done with two tables: one for the order itself and one for the individual line-items that make up the order or invoice. Each line-item refers to the order-ID and contains a second, sequential line-number field as part of its key. This also allows the items to appear, on a printed order, in the same sequence they were entered.

    In an accounting system, payments are handled by "credit memos" that are created when the customer pays the invoice or is issued a credit. These credit-memos are applied to the open invoices. It can, ahh, get messy.

    But you're thinking along the right lines. I suggest strongly that you discuss your issues and concerns with your professor now, because he/she will be delighted to have a student who's obviously thinking about and working on the problem. You're very likely to see something related to your comments brought up by the instructor in a future class. (That's a breath of fresh air to an instructor.)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Nov 2003
    Posts
    3

    Unhappy Re: Is this a good database for a store?

    I changed a few things and still want to know if this database looks good. Does anyone notice any mistakes or things missing like primary keys and more fields? Did I do 3rd normal form correctly?
    I have a few fields in two of the tables that I am unsure of whether or not they go there, so any suggestions with those would be great

    here is what i've changed, any help would be appreciated

    Product Status Table
    productID (Primary Key)
    productQuantityAvailable (Primary Key)
    reorderLevel
    reorderQuantity
    supplierID (not sure if i should put this here)
    productUnitsOnOrder (not sure if i should put this here)

    Purchase Table
    purchaseID (Primary Key)
    customerID
    employeeID
    datePurchased

    Purchase Detail Table
    purchaseID (Primary Key)
    purchaseDetailID (not sure about this)
    productID
    quantityPurchased
    productPrice

    Payment Method Table
    paymentMethodID (Primary Key)
    paymentMethodDescription

    Payment Table
    paymentID (Primary Key)
    purchaseID
    paymentMethodID
    paymentDate
    amountPaid

    do these tables along with other tables look ok (3nf, proper keys, etc.)

    thanks again
    brian
    Last edited by brian721; 11-08-03 at 18:58.

  4. #4
    Join Date
    Nov 2003
    Posts
    3

    Re: Is this a good database for a store?


    Telephone-numbers are often a problem and there's no easy answer to them. Many systems only store two or three, using separate columns. But inevitably some customer comes along who's sprouted cell-phones from every extremity and demands to be called on each one.
    LOL
    I just put all of those telephone tables in, because you are so right about what you said.


    Usually a "purchase" is done with two tables: one for the order itself and one for the individual line-items that make up the order or invoice. Each line-item refers to the order-ID and contains a second, sequential line-number field as part of its key. This also allows the items to appear, on a printed order, in the same sequence they were entered.
    Ok, i see what you're saying...i split the purchase into 2 tables, see my above post
    I have one for the actual purchase and the other one for the purchase details. It looks ok, but I am still not 100% sure. The second line-number field for the other part of the key you talked about, is that just another field i should put in like a purchaseDetailID or something?


    In an accounting system, payments are handled by "credit memos" that are created when the customer pays the invoice or is issued a credit. These credit-memos are applied to the open invoices. It can, ahh, get messy.
    yuck
    In my post above I thought I might of had something with the payment method table and payment table
    I will definitely see the professor about that then as to avoid going crazy


    But you're thinking along the right lines. I suggest strongly that you discuss your issues and concerns with your professor now, because he/she will be delighted to have a student who's obviously thinking about and working on the problem. You're very likely to see something related to your comments brought up by the instructor in a future class. (That's a breath of fresh air to an instructor.)
    Thank you for your time and help sundialsvcs
    Brian
    Last edited by brian721; 11-08-03 at 19:01.

Posting Permissions

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