SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Feb 2004
    Posts
    291
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Primary key for simple tables

    Some (or many) tables in your application are often very simple and contain only two attributes. For instance one field for primary key, which is either an abbreviated-code or an auto-increament-value, and a second field for description.
    At least, this is how I have done it many times. But now I am starting to think (finally!) just to drop the first attribute, and only have one attributes in the tables, which is the description field and set it as the primary key.

    What do you think about this issue? Are you designing tables with only a single attribute?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm having a hard time visualizing what you are getting at. can you show a before-and-after sample?

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Location
    Wolsztyn, Poland
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So simple tables are mainly used for dictionaries. What about deleting an element from a dictionary using some web interface? Normally you pass a PK through GET or POST to the script which then runs a proper query and deletes the record.
    Without artificial primary key you would have to pass the description field as a PK. It doesn't seem to be handy. I prefer using artificial PKs.. they don't take much space and make life easier.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by materix
    What do you think about this issue? Are you designing tables with only a single attribute?
    i think it's a great idea, assuming you have no problem with bloat

    i don't personally do it myself except for certain special tables, like Keywords

    it's important to make sure that you have PK/FK integrity defined for the relationship of main table to lookup table

    otherwise you might as well not have a lookup table at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Feb 2004
    Posts
    291
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Longneck: Here is an example:

    This is typical:
    Code:
    CREATE TABLE `Dictionary` (
      `Id` mediumint(8) unsigned NOT NULL auto_increment,
      `Word` varchar(40) NOT NULL,
      PRIMARY KEY  (`Id`)
    )
    But why not do it like this?:
    Code:
    CREATE TABLE `Dictionary` (
      `Word` varchar(40) NOT NULL,
      PRIMARY KEY  (`Word`)
    )
    [pl]FiDO:
    Without artificial primary key you would have to pass the description field as a PK. It doesn't seem to be handy.
    Why is it not handy?

    r937:
    i think it's a great idea, assuming you have no problem with bloat
    Sorry, my english is not so good. What do you mean by "problem with bloat"?

    I am hoping to identify all the cons of designing tables with only one attribute, so I will not regret this design-decision later one.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let us say you have a data table of 25,000 rows

    each row has a foreign key to the dictionary table

    if you use a surrogate Id MEDIUMINT key, the foreign keys will require 25,000 x 3 bytes = 75,000 bytes

    if you use the natural Word VARCHAR(40) key, the foreign keys will require up to 25,000 x 40 bytes = 1,000,000 bytes

    so your data table will be much larger, i.e. bloated

    another disadvantage of using the natural Word foreign key is that if you need to change the spelling of a word, you have to cascade the change to all the data rows that contain that value, whereas with a surrogate key, you just update the single value in the Dictionary and you are finished
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Feb 2004
    Posts
    291
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Good points, r937.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Location
    Wolsztyn, Poland
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by materix
    Why is it not handy?
    Lets say you have a record "a very long description of the record" (or even longer) in your dictionary. And now you want to delete it. How will you do it ?
    Passing such a long value through URL is not handy IMHO.. (unless you like urls like ../delete.php?v=a%20very%20long%20description%20of%20the%20record)
    Besides.. if you display a table with records from such dictionary, you have to put each value at least twice in html. Once for the user (usually in a table cell) and once in a link which deletes the record. What a waste of space...


Bookmarks

Posting Permissions

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