I'm stumped. I have a typical small company database designed for a transportation company. I have the tables busted out as follows:
as well as a few others. But those are the main tables involved in my dilema.
Our Orders table, as it stands right now, has foreign keys linked to the Carrier that hauls the product, the Customer that the shipment gets billed to, and the consignee that the product arrives at.
I encountered a problem the other day where we actually delivered a shipment for one of our carriers to one of our customers. So, the carrier has become the customer, and the customer became the consignee.
How do I avoid duplicating the two entities into two different tables? I can't use the primary keys assigned to them because they have been used in the contradicting tables already. Any help would be appreciated, let me know if you need more info. Thank you.