Help designing Order Database (especially relationships between tables)

Posted on 2012-08-21
Last Modified: 2012-08-21
I am trying to figure out how to create a simple order database, i think that the tables i have so far seem to be right. What I am having problems with is relating the tables

please see attached ERD
Question by:futureDBA
    LVL 34

    Accepted Solution

    It is hard to follow your column names, so I am going to take a guess here.

    In the VENDORS table, the VENDOR_ID should be the primary key (not a foreign key).

    In the ORDER table, TPNUM should be the primary key (I assume that is like an order number).  VENDOR_ID should be a foreign key to the VENDORS table, not the other way around (your design shows that a vendor can only play one order ever).

    In the ORDER_DTL, there should be a line number field and the combination of the TPNUM and line number would be the primary key.  The foreign key would go from ORDER_DTL to ORDER, not the other way around.  I'm not sure CUST_ID belongs in this table, are you saying that each line of an order can go to a different customer?  You also have a date in the ORDER and ORDER_DTL tables, not sure what the difference is.  The column names should be changed to have some more meaning.

    Also, you have plural table names (VENDORS, ITEMS) and singular table names (ORDER, CUSTOMER).  The names should be more consistent (all singular or all plural).
    LVL 20

    Assisted Solution

    What is your problem ?

    I don't know your business but the ERD raises some questions

    I would expect an order to have 1 or many order_details , you have the relationship the other way.

    I would expect an order to be an agreement between a vendor and a customer to  buy 1 or more items. So I would expect a relationship between customer and order and not from customer to order_detail.

    I hope for the vendor that he/she gets more then 1 order (as in your ERD) or he/she will soon be out of business.

    I would also except a 1 to many relation from vendor to item.

    Your table definitions  especially your primairy keys will have to change .
    If a customer can place more then 1 order on a day at a vendor think about the date in "order"  , register with seconds or you will need another identifier (wat is tTPNUM ?),
    if a customer can place more then 1 order within a second at a vendor think about using timestamp-format for tdate.
    LVL 20

    Expert Comment

    Typing took to long , you where faster Johnsone

    Author Closing Comment

    thank you, both answers  equally helpful

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now