Help designing Order Database (especially relationships between tables)

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
item.png
futureDBAAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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).
0
 
flow01Connect With a Mentor Commented:
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.
0
 
flow01Commented:
Typing took to long , you where faster Johnsone
0
 
futureDBAAuthor Commented:
thank you, both answers  equally helpful
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.