Lookup Table or Lookup Tables
January 17, 2012 8:38 PM Subscribe
Database lookup tables...one per relationship or a general lookup table?
I tend to have a single lookup table on my lightly used database applications. The table would be constructed something like this:
lookup_id (auto increment)
lookup_parent_table (table name for one half of the relationship)
lookup_parent_id (row being referenced from above table)
lookup_child_table (table name for other half of the relationship)
lookup_child_id (row for that)
Sometimes I add a couple of columns to contain information about the relationship, like order (if there is one parent and many siblings and they need to be organized).
I index it on parent_table, child_table.
This table can obviously have a lot of rows. It's nice for me because any new tables added to the database don't need their own lookup table, they can use this one, and queries are somewhat standardized. However, I can't get over the nagging feeling that I'm Doing It Wrong and every parent table/child table relationship is supposed to have its own lookup table.
What say you?