• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 64 views
  • Users 0 members are here
Related

Philosophical Question: Local Tables and foreign keys

Gawain Lavers
$organization

When I set up custom System Table, the values often refer to other tables for making selections.  I configure this in TR_REFERENCE_COLUMN.  What I don't do is establish proper foreign key relationships.  I'm a little torn about this.  What do people think?  The arguments I can see for doing it:

  • Makes your dependencies clear.
  • Provides a constraint: generally this would come up if you try to delete a value, say a Step type, that you might referenced, and also would protect during back-end updates and inserts.

Against I can think of:

  • Waste of time, since 99.9% of operations will be protected by the System Tables interface.
  • Dangerous: could, for instance, prevent an upgrade from completing if it needed to update a column you were keying to.

  • $organization

    Firstly, philosophical questions are my JAM!!  Love them.

    I have created many custom system tables.  I also configure them in TR_REFERENCE_COLUMN obviously.  But I do still also create proper foreign key relationships.  Always.  You have stated a few pros there, so I will not go into that.

    Against your cons, what I would say is this:

    • To your "dangerous" comment:  theoretically, you are correct that it could prevent an upgrade from completing.  Is there another reason it is dangerous?  The idea of custom tables is to be used, and if I am creating a table that will be used, that means that the information in said table IS important, which means that it SHOULD be considered, so if it causes issues during some form of modification, then I DO need to pause and consider what that is and how to handle that situation.
      • To speak directly to upgrades:  I have never once had an issue during upgrades due to my many foreign key relationships.
      • Worst case scenario, again, you would notice this issue in your TEST database first, figure out how best to handle it, and solve it there, thus eliminating a true worry about the upgrade completing successfully as it would have been resolved in LIVE by the time you get there.
    • To your "waste of time" comment:  this is a philosophical question!  Philosophically, we should always adhere to the ideal!  Grinning  Yes, I agree that, if the System Tables are set up correctly that it could be a slight waste of time.  That said, it is not like setting up a foreign key relationship is crazy difficult.  It is, in fact, relatively easy to do.  So why not do it just to be safe?
      • Besides, often I have found that I need to pre-populate for use by staff with information and I choose to do that via the database, and sometimes those have helped me to ensure that I do not insert incorrect data in that fashion.

    Just my thoughts!  Practically speaking, again, you are generally correct, you should be able to leave it sit and be fine.  After all, we do not glue our plates to the table to keep them from spilling; gravity takes care of that just fine.  Then again, a database sometimes has little kids playing near it, and you never know when you are going to bump the table...

    John

  • $organization

    Declare foreign key constraints when keying to another one of your local tables, but don't do this when keying on a Tessitura base table. Technically speaking, establishing a foreign key relationship qualifies as affecting the schema of your target table, which is, of course, "unsupported". I don't like to get into unsupported territory unless I have an exceedingly good reason to, and that usually means I will commit to doing a (unless you've automated it) complicated and risky routine of undoing and redoing these schema changes before and after every service pack installation. This could also affect being able to delete objects from within the Tessitura Client if you have rows in your local reftable that are referencing that object.

    That said, once you cross the "firewall" into local table land--why not give yourself schema guarantees when you have foreign key relationships among local tables?