Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    do lookup tables have a name?

    hi,

    do we have a name for short tables having fixed data. by fixed data i mean lookup data. say i have a table:
    Code:
    
    table: students
    --------------------------------------------
    student_id | student_name | student_type
    --------------------------------------------
    and i have referenced student_id with a lookup table

    Code:
    table: student_type_lookup
    --------------------------------
    student_type_id | student_type
    --------------------------------
    1               | REGULAR
    2               | CORRESPONDENCE
    ---------------------------------


    question is, what do we call the second table? like we may call the first table student_master, its associated table student_fee_detail etc.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    they are commonly called lookup tables, but i think you're asking about a convention for naming them

    (aside: someone else on this forum is fond of saying that there's no such thing as a lookup table, it's always an entity of some type, but that's a nuance that you may not be ready for)

    i would call the table student_types
    Code:
    table: student_types
    -----------------
    id | student_type
    -----------------
    1  | REGULAR
    2  | CORRESPONDENCE
    i always name my tables in the plural, but opinions on this point differ

    i always use the name "id" for a surrogate identifier in the table in which it's the primary key, and then entity_id as a foreign key name in tables referencing it

    so,
    Code:
    table: students
    --------------------------------------------
    student_id | student_name | student_type_id
    --------------------------------------------
    this convention allows me to spot "lookup" relationships easily when reading sql

    more generally, any one-to-many relationship is easily spotted this way

    this is something you don't get if you use the name entity_id as both primary and foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    "Two Cents" and "Horses For Courses" time.
    I've seen lookup tables also referred to as Reference Tables, so that's another convention.

    I've switched back and forth on use of plurals for naming tables. The option would be easy if the damn English language was more consistent on how to pluralize nouns, but since it isn't, pluralizing object names can make automated code generation very problematic.

    I never use simply "ID" as a field name. It's always "[TableName]ID", and my foreign key values have the same name as well. OK, one-to-many relationships don't pop out at you so obviously, but then you'd have the same deficiency referencing natural keys as well, and its not a big deal compared to the risk of confusing which particular "ID" column you are referencing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    ... and its not a big deal compared to the risk of confusing which particular "ID" column you are referencing.
    risk? SELECT foo.id AS foo_id FROM ...

    i *heart* you, by the way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    i *heart* you *back*, you Crazy Canuck.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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