Multiple foreign keys to a single column

Question

I'm defining a database for a customer/ order system where there are two highly distinct types of customers. Because they are so different having a single customer table would be very ugly (it'd be full of null columns as they are pointless for one type).

Their orders though are in the same format. Is it possible to have a CustomerId column in my Order table which has a foreign key to both the Customer Types? I have set it up in SQL server and it's given me no problems creating the relationships, but I'm yet to try inserting any data.

Also, I'm planning on using nHibernate as the ORM, could there be any problems introduced by doing the relationships like this?

Answer 1

A foreign key can only reference a single primary key, so no. However, you could use a bridge table:

CustomerA <---- CustomerA_Orders ----> Order
CustomerB <---- CustomerB_Orders ----> Order

So Order doesn't even have a foreign key; whether this is desirable, though...

Answer 2

No, you can't have a single field as a foreign key to two different tables. How would you tell where to look for the key?

You would at least need a field that tells what kind of user it is, or two separate foreign keys.

You could also put the information that is common for all users in one table and have separate tables for the information that is specific for the user types, so that you have a single table with user id as primary key.

Answer 3

You can create a foreign key referencing multiple tables. This feature is to allow vertical partioining of your table and still maintain referential integrity. In your case however, this is not applicable.

Your best bet would be to have a CustomerType table with possible columns - CustomerTypeID, CustomerID, where CustomerID is the PK and then refernce your OrderID table to CustomerID.

Raj

Answer 4

Search for articles on "generalization specialization relational design".

Two customer types each with its own attributes is a classic case of specialization. Some of the articles will give you very detailed possible design ideas. Pick the best one for your case.

Answer 5

As noted, if the key is, say, 12345, how would you know which table to look it up in? You could, I suppose, do something to insure that the key values for the two tables never overlapped, but this is too ugly and painful to contemplate. You could have a second field that says which customer type it is. But if you're going to have two fields, why not have one field for customer type 1 id and another for customer type 2 id.

Without knowing more about your app, my first thought is that you really should have a general customer table with the data that is common to both, and then have two additional tables with the data specific to each customer type. I would think that there must be a lot of data common to the two -- basic stuff like name and address and customer number at the least -- and repeating columns across tables sucks big time. The additional tables could then refer back to the base table. As there is then a single key for the base table, the issue of foreign keys having to know which table to refer to evaporates.