• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 66 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.

Parents
  • $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

Reply
  • $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

Children
No Data