Menu
Suppose I have two tables, Customer and Vendor. I want to have a common address table for customer and vendor addresses. Customers and Vendors can both have one to many addresses.
Add columns for the AddressID to the Customer and Vendor tables. This just doesn't seem like a clean solution to me.
Customer Vendor Address
-------- --------- ---------
CustomerID VendorID AddressID
AddressID1 AddressID1 Street
AddressID2 AddressID2 City...
Move the foreign key to the Address table. For a Customer, Address.CustomerID will be populated. For a Vendor, Address.VendorID will be populated. I don't like this either - I shouldn't need to modify the address table every time I want to use it for another entity.
Customer Vendor Address
-------- --------- ---------
CustomerID VendorID AddressID
CustomerID
VendorID
I've also seen this - only 1 foreign key column on the Address table with another column to identify which foreign key table the address belongs to. I don't like this one because it requires all the foreign key tables to have the same type of ID. It also seems messy once you start coding against it.
Customer Vendor Address
-------- --------- ---------
CustomerID VendorID AddressID
FKTable
FKID
So, am I just too picky, or is there something I haven't thought of?
741
Entity Mappings If you want to map the same database table to two entities, you should create a simple inheritance hierarchy. The superclass should be abstract and contain all attributes that are shared by both entities. You should map it as a mapped superclass so that it is not an entity itself.
Many-to-many (m:n) relationships add complexity and confusion to your model and to the application development process. The key to resolve m:n relationships is to separate the two entities and create two one-to-many (1:n) relationships between them with a third intersect entity.
To implement a one-to-many relationship in the Teachers and Courses table, break the tables into two and link them using a foreign key. We have developed a relationship between the Teachers and the Courses table using a foreign key.
How to implement one-to-many relationships when designing a database: Create two tables (table 1 and table 2) with their own primary keys. Add a foreign key on a column in table 1 based on the primary key of table 2. This will mean that table 1 can have one or more records related to a single record in table 2.
I'd say the missing piece of the puzzle is the "is a" relationship that is often overlooked in data modeling; this is distinct from the familiar "has a" relationship. An "is a" relationship is similar to an inheritance relationship in a object oriented design. To model this you'll need a base table that represents the common attributes of vendors and customers. For example, we could call the base table "Organizations":
Organizations Vendors Customers
-------------- --------------------- ---------------------
OrganizationID(PK) OrganizationID(FK/PK) OrganizationID(FK/PK)
AddressID1(FK)
AddressID2(FK)
In this example Vendor "is a" organization, and Customer "is a" organization, whereas an organization "has a" address. The Organizations, Vendors, and Customers tables share a common key and a common key sequence enforced by referential integrity.
93
I think out of the three options you gave, I'd be most inclined to go with option 1. Normally a customer or vendor won't have more than a few different addresses, but if they do, maybe the solution below would work better for you. I wouldn't go for option 2, because it probably doesn't make sense to associate an Address with both a Customer and a Vendor at the same time. I know you'd probably only set one of those IDs at a time, but the model might be confusing, and you may need to add special logic to make sure only the CustomerID or the VendorID is set on any given record. I would definitely not do option 3, because you can't make FKID a true FK. If you want a column to reference more than one table, you will not be able to use a FK constraint in the database to enforce it. Plus, if you plan on using an ORM to interact with the database in code, they tend to have trouble dealing with "fake" foreign keys that reference multiple tables depending on a separate "discriminator" column.
If you want a truly open-ended solution, you could create many-to-many relationships between Customer and Address and Vendor and Address.
Customer
--------
CustomerID (PK)
Vendor
------
VendorID (PK)
Address
-------
AddressID (PK)
CustomerAddress
---------------
CustomerID (FK/PK)
AddressID (FK/PK)
VendorAddress
-------------
VendorID (FK/PK)
AddressID (FK/PK)
How about having four tables with one acting as a gateway to the addresses? So you would have
Customer
customerId (PK)
addressBookId (FK to AddressBook)
Vendor
vendorId (PK)
addressBokId (FK to AddressBook)
AddressBook
addressBookId (PK)
Address
addressId (PK)
addressBookId (FK to AddressBook)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
