Tuesday, October 15, 2013

So much for your understanding diversity goal this year.

It's nothing but foreign keys.  I call it the immigrant table.

The only time I've avoided Foreign Keys in a database was during a Biztalk (XSL) to SQL Server updategram where I had multiple levels of relation.  Leaving out the foreign keys made a certain amount of sense when it came to the inserts which, given that we wiped the tables on a fairly frequent basis because the data was fleeting, were the longest running portion of the application when left in place.  However, the updategrams enforced the non-existent keys, so we had them, they were just on the contract with the database instead of the database itself. I still think the following is one of the dorkiest things I've ever written in my life given it basically says append a const to the end of your string, "The fix (in BTS) involves mapping the iterator functoid to a new scripting functoid in the MAP that takes the iterator int(eger) as a parameter and just appends "CPID", or any other constant you feel won't be replicated elsewhere, to the end so that it becomes something unique you won't see in the XML otherwise (you could probably use a string concatenation functoid somehow instead, but that was just being annoyingly useless in my tests because of the string data type restriction)."

Via SquareCog over at StackOverflow:

Reasons to use Foreign Keys:
  • you won't get Orphaned Rows
  • you can get nice "on delete cascade" behavior, automatically cleaning up tables
  • knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality.
  • FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance
  • they enable all kinds of auto-generated support -- ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc
  • someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented
Reasons not to use Foreign Keys:
  • you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn
  • by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. (Granted, in such cases, what you are trying to do is create an Orphaned Row, and that's not usually a good thing). This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state (but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?).
  • sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it
  • you are just being lazy :-)
Snrky: It's nothing but foreign keys.  I call it the immigrant table.
Title: So much for your understanding diversity goal this year.

No comments:

Post a Comment