Hi there,

I have a basic database architecture question that I hope to consult everyone here on.

Let's say I have an SQL table for polling/voting data (2 options ):

Vote:
vote_id(PRIMARY)
vote_topic(VARCHAR)
vote_option1(INT)
vote_option2(INT)


However, later on, if there are more options of voting data such there there can be more vote_option fields, the current table wouldn't suffice.

Therefore, is it a good idea to restructure the Vote table to such :

Vote:
vote_id(PRIMARY)
vote_topic(VARCHAR)
vote_option_no(INT)
vote_option_data(VARCHAR)


In this case, the vote_option_no field will keep track of the number of voting options, and vote_option_data will be a string containing all the vote_option data separated by , e.g. a delimiter like the | character( 2|34|5|3 ) ?


Is this the right way to solve such a problem ?

Thanks!
IZ

Why don't you dernormalize them ?
Creating a one to many link between the vote_id(one) to the options (many)

This is how I would write it in postgresql:

create table vote(
 vote_id integer not null,
 vote_topic VARCHAR(255) not null,  primary key (vote_id)
);

create table options(
  option_id integer not null,
  vote_id integer not null references vote(vote_id),  --This create a foreign key to vote.vote_id
  option_value varchar(100),
  constraint primary key (option_id)
);

And the query to get back everything would be:

select v.*, o.option_value
from vote v
  left outer join options o on o.vote_id=v.vote_id
where v.vote_id={something}

This way, you would get back as many rows as you have options saved for that specific poll, without having to worry about changing your schema if more options are to be added.

Yes that is the way your table should be structured.

Originally Posted by Insane Membrane

In this case, the vote_option_no field will keep track of the number of voting options, and vote_option_data will be a string containing all the vote_option data separated by , e.g. a delimiter like the | character( 2|34|5|3 ) ?


Is this the right way to solve such a problem ?

No, it's not, but it's the Special Olympics way to solve such a problem. Meaning no offense to the Olympians. Or to you. It's just the absolute wrong way to approach anything like this. One of the first things you learn in database 101 is you never do this.

Tables are delimited into rows and columns so that you don't have to hack data together in this manner.

You may also use subquery to do so.

I wouldn't say never...

There are performance reasons to sometimes denormalize.

If he has 10,000 voting records and each one has 0-1,000 options, and the options are all different, not re-used... the constant rejoining of the two table on lookups could pose a performance problem depending on his hardware.

I have had instances like this where I ultimately chose to have a one-to-many field stored in a single field with a delimiter because of the performance savings.

There are tricks you can do to still query all the relevant data even when it's hacked into one field like this. For example, if you want to load all voting records that have a specific option, and your delimiter on the option field is "|", you can do SELECT fields FROM vote WHERE '|'+vote_option_data+'|' like '%|myoption|%'

But yes, it is a hackjob, it does make for nightmares for the person that comes after you that has to manage the DB, and it should be avoided if at all possible. But it can be done if there's a good enough reason to do it.

If you're going to use a delimiter the way Nyef describes, at least go with XML. Parsing XML is actually faster in many cases, because it calls out to a compiled parser that runs very close to the metal, vs compiled on the fly but not to machine language SQL or worse PHP code to split based on something like a pipe (|) char.

Plus, it's far easier to make sense of, esp for the poor guy who inherits the system!

Performance semantics not withstanding, IMO the coolest way to solve this particular problem is by using bits. For those not quite sure how that would work, I suggest the following. A table with, among other columns, a column of type integer called "VoteBits". A separate table (VoteOptionMaster) with two columns; OptionID (Integer, PK) and OptionDesc (Not Null, varchar2). The VoteMaster table will contain the choices IDs and Descriptions (i.e. 1=Chocolate, 2=Vanilla, 4=Boxers, 8=Briefs, 16=Apples, 32=Oranges, etc). So, then say I voted for Chocolate, Boxers and Apples - I would save the value of 21 (or 1+4+16) in the VoteBits column. Then, you can use a bitwise operator in the query (I would probably write a function to return the associated description for each) to "detect" which bits are set. Since the numbers need to double for each vote choice, there is a limit to the number of choices you can have. I've used this successfully for just this purpose, but would only advise using if the number of possible choices is quite small.