MySQL Forums
Forum List  »  MySQL Workbench

One Table, Referenced Many Times
Posted by: Nathan Wenzel
Date: July 14, 2008 08:05PM

Hello,

I'm new to MySQL Workbench and may be using it incorrectly. I have an application that manages basic shipment information between two "customers" (Shipper and Consignee). I have a single table listing all customers. I have a table that holds shipment information. I want to create a schema that shows the shipment with the relationship to shipper (customer1) and consignee (customer2).

The two tables look something like:

===================
| shipment |
|-----------------|
| PK id_shipment |
| FK id_shipper |
| FK id_consignee |
| shipment_amt |
| ... |
===================

===================
| customer |
|-----------------|
| PK id_customer |
| customer_nm |
| ... |
===================

The SQL to SELECT records would be something like:
SELECT t1.shipment_amt, t2.customer_nm as shpiper, t3.customer_nm as consignee
FROM shipment t1, customer t2, customer_2 t3
WHERE t1.id_shipper = t2,id_customer AND t1.id_consignee = t3.id_customer

I can copy the customer table, but then it becomes a new table. I'd like it to stay synced with the original table. Is Workbench the right tool to create a diagram to show the relationship with the two customers when both customers are held in the same table? Or perhaps a better question, what I am doing wrong?

Thanks,
Nathan

Options: ReplyQuote


Subject
Views
Written By
Posted
One Table, Referenced Many Times
3281
July 14, 2008 08:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.