Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    127

    when to choose lookup table over check constraint for embeding values

    Hi

    im designing relatively simples datamodels although im not sure what is the best appraoch to take when concerning a column that has an absolute number of possible values.

    should i embedd values within a CHECK constraint, or alternatively, should i enbedd these values into a LOOKUP table? Obviously (or to me, anyway) if i have less-that-five or so values, then i'd use a CHECK constraint for convenience, although reasonably, what number of values or reasons (added table joins etc) would constitue the use of a LOOKUP table.

    this may appear trivial, but i'm really interested in peoples views,

    thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    It's a tricky decision. The most important factor is how static the list of values is. If it is highly static then there is little to be gained from having a separate table; you can build static selectors (e.g. radio groups) into your application secure in the knowledge that you will probably never have to change them. On the other hand, if it is very (or even fairly) likely that new values will be added to the list at a later date, then using a lookup table and basing your application's selectors as queries against that table makes your application more robust in the face of changes to the constraint.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You should also consider whether or not the domain values will be accessed independently.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Imho

    Well, I used to believe that five-or-so fixed values was better in a CHECK constraint rather than a table. But many experiences have changed my mind. Now, for more than one value, I use a table every time. There are many reasons, but the most important (ie. the most common reason for changing an existing CHECK constraint into a table) is report tasks using the db. If you have (example):

    ClientType table (instead of CHECK constraint on ClientType col in Client table)
    ClientType Description
    F Fat
    T Thin
    G Tight

    The report programs that need the description column do not have to have their own (programmatic) mapping of ClientType to Client.Description; such mapping is duplicated and presents an administration problem. Plus the Description can be changed easily, and is immediately reflected in the reports.

    Of course, ClientType is an FK column in the Client table.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •