I am using a table to centralize the addresses. Customers and Suppliers have a reference to address.
A Customer has an Address, an Address may or may not be associated with a Customer.
A Supplier has an Address, an Address may or may not be associated with a Supplier.
To ensure that an address is not associated with more than one Customer or Supplier, I have a unique index on the Customer and Supplier tables on AddressID column.
I am suspicious that this relationship is abnormal, because I am not able to map it using Entity-Framework with FluentAPI.
- In my real scenario, the address table will have many more columns. In fact this is an adaptation to simplify a complex scenario where the address table is a financial release and the tables customer and supplier are representing the origin of the financial release, like Sale and Purchase.
Your model seems reasonable. The merits of having a second table when you want to enforce a 1-1 relationship may not be obvious to everyone.
I can think of two good reasons off-hand:
- You want the addresses in one place so you can treat all addresses equivalently (say geo-coding them, standardizing them, extracting features).
- The address column is long and many queries do not require it, so you gain efficiency by not storing the address with the rest of the data ("vertical partitioning").
And there may be other reasons. I can't speak to why EF makes such relationships difficult to express.