Design Pattern required for database schema with two classes both composing a third class.

Question

Consider a system which has classes for both letters and people; both of these classes compose an address. When designing a database for the system it seems sensible to have a separate schema for the address but this leads to an issue; I don't know how to have a foreign key in the address table cleanly identify what it belongs to because the foreign key could identify either a letter or a person. Further more I expect that further classes will be added which will also compose an address.

I would be grateful for design patterns addressing this kind of design point.

Best Regards

Answer 1

Surely the foreign keys should be from the Letter and People tables referencing the primary key on the Address table?

So, the Letter table contains an AddressId column referencing the Id on the Address table, as does the Person table, and any future classes which compose an address.

If Letters and Persons compose multiple addresses, then intermediate link tables will be required.

Answer 2

I don't know how to have a foreign key in the address table cleanly identify what it belongs to because the foreign key could identify either a letter or a person.

It sounds like you have got that the wrong way around. There would be no foreign key in the address table; rather, the letters table would have a foreign key referencing the address table and the persons table would also have a foreign key referencing the address table.

In SQL, the REFERENTIAL_CONSTRAINTS view in the Information Schema catalog will tell you which tables are referencing the address table.

In our shop we regularly debate whether an address should be modelled as an entity in its own right. The problem with treating an address as an entity is that there is no reliable key beyond the attributes themselves (postal code, house name or number, etc) and many variations can identify the same address (I can write my home address twenty different ways). Then you need to consider post office boxes, care of addresses, Santa Claus, etc. And the Big Question: do you allow an address to be amended? If someone moves house, do they keep the same address entity with amended attributes (that way all linked entities get the address change) or do you lock-down addresses' attributes and force the creation of a new address entity then relink all the referencing addresses to the new one (and do you delete the now-orphaned address entity and if yes then why did you bother to replace it...?) But your application still needs to allow an address to be amended in case the post office changes it in real life, but how to you prevent this ability from being misused i.e. using it for aforementioned illegal house moves? You can use a webservice to scrub your address data so that is is correct but an external system way have less clean data and therefore you can't get your address entities to match anymore...?

I like to keep it simple: an address is a single attribute being the plaintext you must put on an item of mail for it to be delivered by the post office to the addressable entity in question; it is not an entity in its own right because it lacks an identifier. For practical reasons (e.g. being able to print it on an address label), this single attibute is usually split into subatomic elements (address_line_1, address_line_2, ... postal_code, whatever).

Because most SQL products lack support for domains, I have no problem duplicating the column names, data types, constraints, etc between for each table that models an addressable entity.