Help designing Order Database (especially relationships between tables)

Posted on 2012-08-21
Medium Priority
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
  • 2
LVL 35

Accepted Solution

johnsone earned 1000 total points
ID: 38316903
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 21

Assisted Solution

flow01 earned 1000 total points
ID: 38316952
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 21

Expert Comment

ID: 38316964
Typing took to long , you where faster Johnsone

Author Closing Comment

ID: 38317022
thank you, both answers  equally helpful

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

839 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