0

Looking for opinions and feedback for where relationships should be defined between database tables. Specifically, whether to prefer direct relationships where they conceptually make sense and need to be used, or to prefer using the minimum number of relationships possible.

For example, in this diagram:

enter image description here The a_g relationship really needs to exist. The d_g relationship conceptually makes sense and will be used often. My question is whether to have such a relationship, or whether to omit it in preference of going d->c->b->a->g.

More relationships helps performance, but seems to me to add a risk of inconsistency. It adds the risk that d->g could get to a different g than d->c->b->a->g. (Obviously this particular design dictates that the g will be the same either way, but I'm talking about if something goes horribly wrong - those "how the heck did this happen" situations.)

Likewise, when working with d, a will often need to be accessed in a situation where the data in c and b don't need to be accessed. Should there be an a_d relationship, or is starting to create those "time saving" relationships a way to get out of hand?

Perchik
  • 5,262
  • 1
  • 19
  • 22
jamespharvey
  • 63
  • 4
  • If anyone could make that link an in-line image I'd appreciate it. (I don't have enough reputation to do that, yet.) – jamespharvey May 30 '14 at 20:47

1 Answers1

0

Please think about increasing of difficulty at population or maintenance of group of tables, that have too many referential integrity constrains, especially if there are dependencies such as loops. You must provide in documentation sequence(s) of tables, that must be followed for population or maintenance. A dangerous alternative would be to turn off some or all referential integrity constraints temporarily and don't forget to activate them all again after maintenance.

Mart Rivilis
  • 122
  • 4