Variable PK for specific value

anjanesh · #1 (**permalink**) 12-18-06, 00:48

This is my table structure

Code:

CREATE TABLE `poll_results` (
  `PollID` bigint(20) NOT NULL,
  `UserID` bigint(20) NOT NULL,
  `OptionNo` int(11) NOT NULL,
  `Entry` datetime NOT NULL,
  PRIMARY KEY  (`PollID`,`UserID`,`OptionNo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The reason for `OptionNo` included for PK is to use it for multiple-select polls.

I have the first entry in a `Users` table with UserID = 0 is for Anonymous user.
How do I get this to work such that its
PRIMARY KEY (`PollID`,`UserID`,`OptionNo`) for every `UserID` thats not 0 and
PRIMARY KEY (`PollID`,`OptionNo`) for every `UserID` thats 0.

Thanks

Yveau01 · #2 (**permalink**) 12-18-06, 03:08

... by redesigning it. This is not going to work, you can't declare a 'variable' PK. Problem is that you're trying to implement something that is data related. That's wrong, always was wrong and will be wrong in the future. A database design should be completely data unrelated. So using 'special' ID's etc (like your 0 for anonymous users) should not influence the database model.

So my advice: back to the drawing table and try another aproach. This is not going to stand the test of time.

Gr,
Yveau

Internet Services

Web Development

Digital Marketing

Consumer Tech