CODEWITHSUNDEEP
phpmysqllookup-tables
Run
Run

Reputation: 57176

Lookup table: any better practice?

I wonder if any better practice or any principle when come to design a lookup table.

I intend to design an abstract lookup table which can serve many different situations.

For instance, I call my lookup table as a masters and slaves table,

CREATE TABLE IF NOT EXISTS `masters_slaves` (
  `mns_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `master_id` varchar(255) DEFAULT NULL COMMENT 'user id or page id',
  `slave_id` varchar(255) DEFAULT NULL COMMENT 'member id or user id or page id',
  `cat_id` varchar(255) DEFAULT NULL COMMENT 'category id',
  `mns_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `mns_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`mns_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

so this lookup table can server the relationship of these types like,

Admins and Members
Admins and Pages
Admins and Posts
Post Categories and Posts
Page Parents and Pages
etc

the cat_id in masters and slaves table will describe and differentiate these categories. for instance, cat_id 1 is Admins and Members and so on.

And I will insert:

  1. admin id into the column of master_id and member id into slave_id column
  2. parent page id into the column of master_id and child page id into slave_id column
  3. post categories id into the column of master_id and page id into slave_id column
  4. etc

But I am sure about it whether I should go for it or not:

  1. Is this a good lookup table practice or should do many create more than one lookup tables for different relationships?
  2. If I can do the lookup table like this, which is only one lookup table for all, what consequences I will have in the future? Will this sole lookup table will be over populated when my site content grows?
  3. Another thing come to mind is that - isn't the tag system a lookup table solution too?

Thanks.

Upvotes: 0

Views: 528

Answers (2)

Ronnis
Ronnis

Reputation: 12833

It sounds an awful lot like the anti-pattern One True Lookup Table. Google it!

Here is a list of bad things I came up with in less than 1 minutes:

  • You won't be able to enforce referential integrity
  • All relation have to be either many-to-many or one-to-many
  • You won't be able to handle attributes (100 qty of article_id=1 sold in store_id=7)
  • You will only be able to deal with single column keys
  • The table will be bigger (thus slower on average)
  • The indexes will also be bigger (thus slower on average)
  • It may cause unnecessary contention
  • Optimizer statistics will become skewed
  • Database maintenance becomes harder

I could easily come up with more, but I don't think it's really needed ;)

When one doesn't have much database experience, it feels like a good thing to do, to reuse stuff and use "common" structures, but databases rarely benefit from it.

Use separate tables for your relationships. Eventually, you will do that anyway.

Upvotes: 4

aviv
aviv

Reputation: 2809

From my experience it is better to create a lookup table for each category. Here are the benefits as i see them:

  1. One lookup table might became very large, while a few smaller lookup tables may be loaded by the caching mechanism of the mysql into memory and be handled only from there if you are accessing a special category.
  2. It is easier to load / reload / backup etc' a few smaller tables.
  3. You can later on change the table schema, let's say you wish to add another field only for one type of category. You will not need to add it to all the rows in this global table.

I think there are more pros for having a few smaller tables than one big one.

Upvotes: 1

Related Questions