Database Design - How do I setup an FK relationship to 1 of 2 tables?

Question

What is the best way to maintain a unique ID field across multiple database tables?

My database contains both businesses and people, and both entities need to have a unique ID field associated with it. In addition, there are other tables (for example Addresses) which can reference either a business or a person using their ID as a foreign key.

Some ideas I am thinking of are:

• Use a non-autonumber ID field that gets calculated when a row is inserted. This would solve my problem of being unique, but then anytime I wanted to lookup something by a related property (such as by an address) I'd have to check both tables for which one contains the record I'm looking for.

• Add a Prefix to the AutoNumber ID to identify what table to look for the ID in, however then my ID fields in associated tables would probably either become strings or contain flags for what table they are associated with and I'm not sure how that will affect performance.

• Merge the People and Businesses into a single table. My problem with this is people and businesses have different properties and need separate fields, and this kind of goes against my nature since I prefer to have separate tables for separate entities.

• Create a master table containing a unique ID field, the ID field of either the Person or the Business, and a flag to say which one it is. Then use that ID as my external reference # and in all associated tables.

• Some better way of handling this that I am not aware of since I am not a dba

Whatever solution I go with needs to be able to easily to handle a large number of records (the database this is going to replace has a few million records) and is on MS Sql Server

Answer 1

Businessess and people are truly, completely, separate to the business ?

They have nothing what so ever in common, even not "the day they were born" ?

The business does not treat the both of them as "counterparties" ?

What I'm trying to illustrate is that you look hard enough at the business, without the usually blindening glasses that the average IT (so-called) "professional" usually looks through, then you will very rapidly find the commonalities that you are looking for.

Define a table in your database to record those commonalities (EVEN if it is nothing more than the identification), and make the addresses reference THAT table.

Answer 2

Create a "supertype" table that identifies both businesses and people and reference that table with your foreign key. This is a common pattern for the situation. See: Party Data Model.

Answer 3

Well you can't set up foreign keys with that setup. A single foreign key can't refernce two possible different tables.

I would do one of the following:

You could of course use two separate columns inthe address table froe each of teh two entities. BusinessId and peopleid. FKs can have nulls, so this would be ok. And you can then enforce the FK relationship which will keep you from having data integrity problems.

Or set up a parent table that includes both businesses and people but has very few fields (only those they really have in common - even maybe only a uniqueid and a recordtype) then you can have child tables for business, people, address, etc.

or set up individual child tables - business and then business address, people and people address, etc. Then you don't need to keep the ids unique between the two logical entities.

I forgot one possibility, if you have many to many relationships, you could have Address, Business, People and then some linking tables, BusinessAddress, PeopleAddress. I personally would not use GUIDs if I had a another choice as they can harm performance.

Answer 4

GUIDs are good as unique identifiers, nothing else.
The problem with GUIDs is their size, especially if your DB is going to be huge.
They should not be used in joins (Index, Foreign key,...).
We had this exact DB design that we had to change back to Integers when the number of records became too large.
I would also point out that you need to be careful with your design for your persons/businesses/addresses. It's a many to many relation. A business/person can have more than 1 address, an address can be for multiple businesses/persons...

If you want to keep businesses and persons separate, you can have 2 tables PersonAddress and BusinessAddress to hold the relations and you would have to do a union when looking up an address for both, or you could have a single table EntityAddress for both Businesses an Persons together with an EntityNature field telling if it's a business or a person.

Answer 5

Some databases allow you to have foreign keys with null values. Some do not, and I cannot recall if SQL Server does. If yours allows it, you can have 2 ID columns in the address table, one that points to People and one that points to Businesses. That approach also has pros and cons; one of the cons being that it is probably frowned upon by your DBA, but if your database allows it, then perhaps it could be one of the alternatives.

Answer 6

You could also invert your thinking.

Instead of having Address have the person or business id, the person or business has the address id.

This is a more natural way of thinking about it in my book... a Person HAS an Address.

Answer 7

There's a few different patterns for doing this, but the easiest one and most flexible is to use unique identifiers (GUIDs); most DBs have some facility for building these (SQL Server is NEWID() for example). They're bigger than other ID forms, but they'll do the job you're looking for.

Answer 8

This is what GUIDs are for.