Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    27

    Cool Relating Domain Tables

    When using domain tables (lookup tables) with just a few records, is it better to join them by a logical code name (with cascading updates) or an identity id number?

    As an example, if I am specifying different phone types, am I better off relating the table by "Home", "Business", "Fax", "Cell" or by a separate identity column.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    interesting question, as it can elicit passionate debate

    it's the "natural versus surrogate" argument

    natural keys often have the advantage of not needing to join to the lookup table for comprehension -- in your example, phone number types Home, Business, Fax, and Cell -- whereas surrogate keys force you to do the join

    in terms of performance, natural keys often take up more room and perform worse in indexes

    however, in your case you did say "just a few records" so given that scenario, i'd probably use a natural key

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Nov 2002
    Posts
    27
    Thanks...that is exactly the type of answer I was looking for. Most people just reply "It depends on your needs" without offering any general guidelines.

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Originally posted by jackb
    Thanks...that is exactly the type of answer I was looking for. Most people just reply "It depends on your needs" without offering any general guidelines.
    They'll also say "it depends on your DBMS." One problem with natural keys is that they tend to lead to compound primary keys, which SQL DBMS's don't handle well.

    Try to get the design right first, and then do profiling so you can use objective measures to determine performance.

    In this case, for example, a clever DBMS could very well pool string values behind the scenes so there might be no appreciable increase in size!

Posting Permissions

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