Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Why use a table with only one column?

    Is it common or good practice to have a table with only one column?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Common, no. But it isn't inherently bad practice - it is perfectly valid in principle.

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    What are you putting in the column?

  4. #4
    Join Date
    Apr 2004
    Posts
    4
    Valid states for my app. For example, CA, NY, OR.

    Just wondering if I should have an auto-incremented int ID column as well to serve as the primary key.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Apart from meaning your table now had two columns instead of one, what purpose would that ID column serve? Answer: none. The state code itself is unique and will serve as a perfectly good, natural, primary key. Stick with your one column - or perhaps more usefully, add a description column that tells the user that 'NY' means 'New York' etc.

  6. #6
    Join Date
    Jul 2004
    Posts
    8
    I would convert that table into a multi-purpose table like so...

    Code:
    TABLE: Categories
    Code        Value
    ---------  --------
    MONTH     January
    MONTH     February
      :            :
    MONTH     November
    MONTH     December
    WEEKDAY  Sunday
    WEEKDAY  Monday
      :            :
    WEEKDAY  Saturday

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by g00ber
    I would convert that table into a multi-purpose table like so...

    Code:
    TABLE: Categories
    Code        Value
    ---------  --------
    MONTH     January
    MONTH     February
      :            :
    MONTH     November
    MONTH     December
    WEEKDAY  Sunday
    WEEKDAY  Monday
      :            :
    WEEKDAY  Saturday
    Would you really? You don't care much about data integrity then! What stops the user inserting 'January', 'NY' or 'blue' into a "weekday" column?

  8. #8
    Join Date
    Jul 2004
    Posts
    8
    Quote Originally Posted by andrewst
    Would you really? You don't care much about data integrity then! What stops the user inserting 'January', 'NY' or 'blue' into a "weekday" column?
    This table would be considered a "setup" table.
    So the dumb end-user (ex: data entry) would not be touching it.
    Only a smart admin-user (ex: manager) would be playing with it.
    Most likely the admin would set it up only once.
    Ex: Add WEEKDAY and never add it again. Why? There are no more WEEKDAY!
    Another Ex: There are only 50 STATES. Unless USA is acquiring Iraq as its 51'st state, then its safe to assume we only need to set the STATES up once.
    Last edited by g00ber; 07-13-04 at 12:40.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by g00ber
    This table would be considered a "setup" table.
    So the dumb end-user (ex: data entry) would not be touching it.
    Only a smart admin-user (ex: manager) would be playing with it.
    Most likely the admin would set it up only once.
    Ex: Add WEEKDAY and never add it again. Why? There are no more WEEKDAY!
    Another Ex: There are only 50 STATES. Unless USA is acquiring Iraq as its 51'st state, then its safe to assume we only need to set the STATES up once.
    But I wasn't referring to setting up the data in your "setup" table. I was referring to validating the data entered into all the other tables. Like this:

    insert into customer (cust_id, state, delivery_day) values (1, 'december', 'green');

    The concept I am alluding to is the foreign key. Your design precludes using foreign keys, and I don't like it!

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by andrewst
    Apart from meaning your table now had two columns instead of one, what purpose would that ID column serve? Answer: none. The state code itself is unique and will serve as a perfectly good, natural, primary key. Stick with your one column - or perhaps more usefully, add a description column that tells the user that 'NY' means 'New York' etc.
    I think that I'm probably genetically averse to the use of natural keys, but that aversion has come from experience... Things that "can't" change still seem to change on me, and at the worst possible times.

    The population of California might decide to rename the state as "Ahnold-Stadt" and legislate the use of the abbreviation AS (they've actually done much weirder thing than that). This would cause great consternation, considering that Arkansas already used the state code of AS.

    A natural key solution has no defense whatsoever against this kind of user-induced change. The problem is legislative, so there is no reasoning with it... You simply have to comply.

    From a logical perspective, Tony has a good point and a natural key makes good sense. It still makes me nervous, since what is logical and what the real world requires are often two different things in my experience!

    -PatP

Posting Permissions

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