#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    179
    Rep Power
    15

    Database design question


    Hi all

    When creating a table, I make an autonumber column so I can make it primary key. Then I'll use that number when making relationships. But look at the following.

    I have rows that I'd like to reference as:

    1.1 | some field | some field
    1.2 | some field | some field
    1.3 | some field | some field
    1.4 | some field | some field
    2.1 | some field | some field
    2.2 | some field | some field
    2.3 | some field | some field
    2.4 | some field | some field
    3.1 | some field | some field
    3.2 | some field | some field
    3.3 | some field | some field
    etc....

    What would you recommend as far as creating a primary key?
    An autonumber column,
    A unique decimal column (ie 2.1,2.2 etc),
    or combine two indexed columns like
    1 | 1 | some field | some field
    1 | 2 | some field | some field
    etc.

    By the way, that first number in 1+n.n is a foreign key to another table.

    Sorry if this sounds confusing. I just want to know if you can combine two columns to make a unique key. If you can, are there any drawbacks.

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Posts
    48
    Rep Power
    14

    Lightbulb Increment by Integer


    If you plan to use auto_increment, you will need to specify the key field as INT(size) and NOT NULL. If you want to use the foreign key from another table, plus another field combined to yeild a unique key, that will work too.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    179
    Rep Power
    15
    I was pretty confident that using a foreign key combined with another column would be good enough as a unique key, but I'm dreading the kind of select statements I'll need to use. I will also have another table that will contain two foreign keys and another column to make a unique identifier.

    Thanks for the feedback!

IMN logo majestic logo threadwatch logo seochat tools logo