locked
table A column joins to other tables, but which table depends on column's current value RRS feed

  • Question

  • Hi All,

    I'm a bit confused about this method I see used increasingly, and I don't know what to call it, but it's something of a lookup table that uses a single column to join to other tables, but which other table is determined dynamically by the value of the column for any particular row.  For example, Table A has integer column Col1, as do Tables B, C, and D.  Tables B, C, and D all have their own numeric PK's.  Table B.Col1 value for all rows is "1".  Table B.Col1 value for all rows is "2".  Table C.Col1 value for all rows is "3".  Table A has 3 rows, each with a distinct numeric value in Col1, the values in this case being 1, 2, and 3.

    What's this joining-column-reuse method called?  Is it good, bad, or does it depend?  This is an insurance agency, so it might be standard procedure to create schemas like this, but I'd like to solicit knowledge and advice from DBAs more skilled than the in-house group.  In my experience, I've seen this method used very rarely.

    Thanks,
    Eric B.

    Monday, August 8, 2016 3:48 PM

Answers

  • I suppose my question wasn't as clear as it could've been.  I will submit a new forum question, complete with a simple example.
    Monday, August 8, 2016 10:14 PM

All replies

  • I think a simple example would help everyone understand your situation.  Note - "simple"! It sounds unusual, but the meaning of "by the value of the column for any particular row" is also not clear.  Perhaps you meant "by the value of ANOTHER column for ..."? That is, the value of columnX determines which table to join using columnY.
    Monday, August 8, 2016 4:11 PM
  • I suppose my question wasn't as clear as it could've been.  I will submit a new forum question, complete with a simple example.
    Monday, August 8, 2016 10:14 PM