Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Polls - composite key or another primnary key?

    Hi,

    I'm making a Polls DB, and have come up against an unexpected stumbling block, here's what I've got:

    CREATE TABLE `tblpolls` (
    `plid` smallint(6) NOT NULL default '0',
    `question` varchar(100) NOT NULL,
    `total` smallint(6) NOT NULL default '0'
    PRIMARY KEY (`plid`)
    ) TYPE=MyISAM;

    CREATE TABLE `tblpollans` (
    `pqid` smallint(6) NOT NULL auto_increment,
    `plid` smallint(6) NOT NULL default '0',
    `answer` varchar(100) NOT NULL default '',
    `count` smallint(6) NOT NULL default '0',
    PRIMARY KEY (`pqid`)
    ) TYPE=MyISAM;

    This has a seperate primary key in the tblpollans. But then i thought (light bulb appears above head), why not use tblpollans.plid and tblpollans.answer as a composite key?

    Which method should I use???

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Polls - composite key or another primnary key?

    Originally posted by mythix
    Hi,

    I'm making a Polls DB, and have come up against an unexpected stumbling block, here's what I've got:

    CREATE TABLE `tblpolls` (
    `plid` smallint(6) NOT NULL default '0',
    `question` varchar(100) NOT NULL,
    `total` smallint(6) NOT NULL default '0'
    PRIMARY KEY (`plid`)
    ) TYPE=MyISAM;

    CREATE TABLE `tblpollans` (
    `pqid` smallint(6) NOT NULL auto_increment,
    `plid` smallint(6) NOT NULL default '0',
    `answer` varchar(100) NOT NULL default '',
    `count` smallint(6) NOT NULL default '0',
    PRIMARY KEY (`pqid`)
    ) TYPE=MyISAM;

    This has a seperate primary key in the tblpollans. But then i thought (light bulb appears above head), why not use tblpollans.plid and tblpollans.answer as a composite key?

    Which method should I use???

    Thanks
    If (plid,answer) are unique and stable, I would prefer to use those rather than introduce a surrogate. However, since answer is varchar(100) that suggests it is likely to get updated (e.g. corrected) at a future time, in which case a surrogate may be preferable - particularly if there are other tables that reference this one.

    Out of interest, why does "everyone" prefix all their table names with "tbl" these days? Is there some DBMS out there that doesn't allow sensible table names like "Polls" and "PollAnswers"? To me, it's like naming your pets dogFido and catFelix

  3. #3
    Join Date
    Jun 2002
    Posts
    63
    moocow lol. I was always taught to use prefix's like that, just to make it easier when it came to referencing things.

  4. #4
    Join Date
    Jun 2002
    Posts
    63
    moocow, doens't really work does it, I just noticed d'uh (slaps forehead) cowdaisy. It's a form of caveman talk, ug ug cow daisy hmmmm caveman john.

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: Polls - composite key or another primnary key?

    Originally posted by andrewst
    If (plid,answer) are unique and stable, I would prefer to use those rather than introduce a surrogate. However, since answer is varchar(100) that suggests it is likely to get updated (e.g. corrected) at a future time, in which case a surrogate may be preferable - particularly if there are other tables that reference this one.
    I'd hope that with a poll you aren't going to be changing the answers after the poll has been responded to. Still, it's possible so to be on the safe side you should probably use a surrogate.

    Out of interest, why does "everyone" prefix all their table names with "tbl" these days? Is there some DBMS out there that doesn't allow sensible table names like "Polls" and "PollAnswers"? To me, it's like naming your pets dogFido and catFelix
    Some DBMS's prefix their system tables with 'sys', I'm thinking of MS SQL Server in particular. I found myself using tbl just so that it would sort nicely.

    Also, as we get more and more "features" you start to need a Hungarian notation to keep up with it all. You start with tables and views, then they add triggers, functions, stored procedures, etc...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "nounParis prepIn artThe nounSpring" -- Ten Things I Hate About You

    mythix, you probably don't need a primary key on the answer table at all

    you do need an index on (plid, answer), so using them as a natural pk gives you that

    don't forget, folks, that the purpose of a primary key is so that other tables can refer to it with a foreign key

    in this example, poll answers are counted, rather than being cross-referenced to the individuals who selected them, so unless there is some other table referencing the answer table, you don't need a pk

    thus, no matter whether (plid, answer) is the primary key, or just indexed, in either case you don't need pqid!

    rudy
    http://r937.com/

  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Originally posted by r937

    don't forget, folks, that the purpose of a primary key is so that other tables can refer to it with a foreign key
    The purpose of a primary (more broadly: candidate) key is to uniquely identify tuples (roughly: records) in a relation (roughly: table). The reason I identify that as its purpose is that you can have a relation that is not referenced by any foreign keys, but you can't have a relation without any candidate keys. (Unless it's one of the special identity relations.)

    SQL allows tables to forgo primary keys because it is not directly relational. You should be aware (wary) of the consequences of these compromises.

    If all of the above is greek to you, get an introductory book on relational theory. Check your favorite bookstore for "Introduction to Database Systems" by Date.

    Edit: Implementation note: Note that to get foreign key constraints working in MySQL, you need to use InnoDB. If you want to start a thread in the MySQL section, I'd be happy to go over what I've worked out. (It's finicky, to say the least...)
    Last edited by sco08y; 05-19-03 at 08:45.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    >> you can't have a relation without any candidate keys

    true, oh, so true -- but so what, eh

    >> SQL allows tables to forgo primary keys

    nice, isn't it

    >> You should be aware (wary) of the consequences of these compromises

    well, at least you sound a whole lot more pleasant that that fabian pascal guy


    rudy

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by r937
    >> you can't have a relation without any candidate keys

    true, oh, so true -- but so what, eh

    >> SQL allows tables to forgo primary keys

    nice, isn't it
    No, it isn't nice at all. What does not creating a primary key gain you? About 10 seconds effort. What do you lose? Integrity, usability, etc.
    Without a primary key (or at least unique constraint) this table can end up like this:

    plid, answer, count
    123, 'A', 1
    123, 'A', 1
    123, 'A', 1
    123, 'A', 2
    123, 'A', 2

    What does that mean? It's rubbish. So we want to get rid of the 4 extra, unwanted rows. How do you do that? This must be one of the most commonly asked questions on SQL forums - how to get rid of duplicates. The answer varies from DBMS to DBMS, and is not obvious (hence the number of questions on the subject). The best solution is not to let it happen in the first place - i.e. take the 10 seconds to define a primary key rather than (a) corrupt you data and (b) spend consiserably more than 10 seconds identifying and eliminating the duplicates later.

    It ain't rocket science!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of course it is not rocket science

    it isn't brain surgery either

    so why make somebody go out and read cj date and relational algebra just to create a table (note: table)

    i totally agree about the "how do i remove duplicates" problem but it is unlikely in mythix's scenario

    let us not argue about the true purpose of a primary key

    let us agree that having one is beneficial and that it is easier to declare a primary key than to declare a unique constraint

    but coming back to mythix's example, as to the choice between a surrogate primary key and a natural primary key, the design as shown calls for an index, and a unique constraint certainly gives that

    as for duplicates in mythix's poll, there would never be any

    people who create polls typically never create duplicate answers -- i mean, why would they?

    rudy

  11. #11
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Talking

    Originally posted by r937
    >> you can't have a relation without any candidate keys

    true, oh, so true -- but so what, eh

    >> SQL allows tables to forgo primary keys

    nice, isn't it
    So what's a tough question to answer, but I'll quote your link:

    "3) Lack of keys in tables. If you don't have a key, then you don't have a table. It is that fundamental and simple."

    Also, I've always found SQL's handling of duplicates to be unpredictable. To my mind, it's just so much easier to avoid non-relational constructs because they often don't work in strange and wonderful ways, a la the infamous NULL bug in SQL.

    >> You should be aware (wary) of the consequences of these compromises

    well, at least you sound a whole lot more pleasant that that fabian pascal guy
    His site is great for its factual content, but, isn't this article in the Onion a perfect parody of him and Date?

    Totally off-topic rant that's only relevant if you read dbdebunk.com (Fabian Pascal's website) regularly: Speaking of pleasantness, lack thereof: their whole "Bush stole the election" shtick is getting old. Granted, I'm a conservative Republican, but I've been reading their columns for the last few years and that's the only line they use! I swear to God, any time they need proof of all that is wrong in America they say, "and of course, the sorry state of our country is demonstrated by the 2000 election."

    Is that the worst Supreme Court decision they can come up with? (Dred Scott?) The worst electoral malfeasance? (Nixon and Kennedy?) The most senseless poltical slugfest? (The Clinton impeachement would have been worse even if it hadn't backfired) American aristocracy? (Camelot again!) Maybe you could argue that it shows how democracy was asleep at the wheel... if you could show how either candidate was, at the time, truly inspiring or horrifying.

    So, anyway, I guess my *point* is that it's a bit ludicrous to nail people for not thinking things through when your written opinion of an entire country is based on attitudes one industry and one election. (Note: I'm basing this on what's on dbdebunk.com. I've got a couple of their books, which are blessedly free of their politics.)

    If you want to debate this, I'm game, but maybe we should start a thread in Chat...

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by r937
    of course it is not rocket science

    it isn't brain surgery either

    so why make somebody go out and read cj date and relational algebra just to create a table (note: table)

    i totally agree about the "how do i remove duplicates" problem but it is unlikely in mythix's scenario

    let us not argue about the true purpose of a primary key

    let us agree that having one is beneficial and that it is easier to declare a primary key than to declare a unique constraint

    but coming back to mythix's example, as to the choice between a surrogate primary key and a natural primary key, the design as shown calls for an index, and a unique constraint certainly gives that

    as for duplicates in mythix's poll, there would never be any

    people who create polls typically never create duplicate answers -- i mean, why would they?

    rudy
    OK, if they're not going to read Date then let's just make it easy for them: "Every table must have a primary (or unique*) key. Why? Just because!"

    If by primary you meant surrogate, then I agree with you: no need to introduce surrogate keys when a perfectly sensible and natural key is available as in this case.

    * The trouble with unique keys is that (in Oracle at least) they are allowed to be NULL. So I can do this:

    Code:
    SQL> create table t (id number unique, name varchar2(30));
    
    Table created.
    
    SQL> insert into t values (1,'a');
    
    1 row created.
    
    SQL> insert into t values (null,'b');
    
    1 row created.
    
    SQL> /
    
    1 row created.
    
    SQL> /
    
    1 row created.
    
    SQL> select * from t
      2  /
    
            ID NAME
    ---------- ------------------------------
             1 a
               b
               b
               b
    For this reason I would prefer to say every table MUST have a PRIMARY key.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, sco08y -- i wonder who that onion parody was aimed at?

    my comment about it being nice that SQL allows tables without primary keys -- can you imagine the kinds of trouble people would get into if they were forced to declare a primary key for every table?

    and as for debating -- i'll pass, especially if it involves database theory

    in theory, theory and practice are the same, but in practice, they aren't






  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by r937
    my comment about it being nice that SQL allows tables without primary keys -- can you imagine the kinds of trouble people would get into if they were forced to declare a primary key for every table?
    Actually, I can't! Well, maybe:

    1) They find they can't insert a valid record because it violates the incorrect PK they defined. So they have to redefine the primary key.

    2) They create surrogate PKs on every table and then duplicate everything else. So they still have duplicate data, but at least they have a means to identify and delete it.

    Either of these is preferable to the alternative, in my view.

    If people don't even understand that each row in a table has to be "about" something identifiable (a person, an invoice line, etc.) then they may as well stick to using spreadsheets.

    This really isn't about rarefied, academic theory. It's simply about making sure your database is not a worthless heap of junk.

  15. #15
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Originally posted by andrewst

    * The trouble with unique keys is that (in Oracle at least) they are allowed to be NULL.
    I think this is because NULL is not equal to NULL, and a unique constraint checks for equality.

    A candidate key is defined to identify a single tuple, so it must use slightly different criteria.

    Even if you took NULLs out of the picture, the problem can arise when what something is is not the same as what it is defined as equalling, as with case-insensitivity (a very common case!) or floating-point numbers (since they are only approximations of reals).

Posting Permissions

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