i want to know how should i store the foreign key in order_item table so that it identifies the order and estimate/invoice/purchaseorder it belongs too.
What I have tried:
table name: order relates to (contact, estimate, invoice, shipment) tables.
column name: contact_id (foreign key(referring to id column of the contact table)).
column name: estimate_id (foreign key(referring to id column of the estimate table)).
column name: invoice_id (foreign key(referring to id column of the invoice table)).
column name: shipment_id (foreign key(referring to id column of the shipment table)).
tablename: purchaseorder (this have one to many relationship with order table)
column name: order_id (foreign key(referring to id column of the order table)).
column name: contact_id (foreign key(referring to id column of the contact table)).
解决方案The answers lie with the user requirements. Analyse the requirements, identify the entities, their data attributes, and establish their relationships. Based on the analysis, design the database scheme for your application. Learn:
1. Introduction to database design[^]
2. 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^]