Friday, October 22, 2010

Reverse foreign key relationships

Sometimes you have a master table and some child tables, which have foreign key relationships to the master table.   The child table can’t have a value for the foreign key that is not in the master table, and the master table can’t delete a record that is referred to by records in the child table.  All this is well and good.

But sometimes, a correct representation of the entities the data is embodying requires that every master table record have one (or more) records in every child table.  An example might be a table of products for sale and a table of prices for those products.   A price must refer to a specific product, but every product must have at least one price.

How does one enforce this in the database?

Now you can say, “That’s business logic!  That doesn’t belong in the database!”  But I tend to feel that there’s a fuzzier line between business logic and database integrity rules than is sometimes acknowledged.  Why is “a price must refer to a product” a question of database integrity, but “a product must have at least one price” a business logic issue?


Post a Comment

<< Home