Need help organizing database to track voting...

wxdqz

New Member
I'm trying to develop a db that tracks students at my college and their votes on different topics... So far I have come up with the tables listed below, but I'm not sure if this is the best way to do it.

I have one table (userTable) of users and their properties (sex, college, class, etc). I have another table (topicTable) of each topic they will be voting on, along with a long and short desc. and generic 'option' fields so the choices can range from "a) Yes, b) No" to "a) Strongly Agree, b) Somewhat Agree, ..." and a set of properties that the user should have to be able to vote on this topic (sex, college, class, etc). These answers are then stored in another table (topic000Table) which is unique to each topic and stores the unique id from userTable along with their answer. This method would require me to add a new table every time a new topic was introduced -- is there any better way to go about this project?

Thanks

CREATE TABLE userTable (
id int(5) NOT NULL AUTO_INCREMENT,
studentid char(9),
first char(50),
last char(50),
email char(50),
password char(20),
sex char(1),
college char(3),
class char(4),
campus char(1),
housing char(2),
status char(1),
key(id)
);

CREATE TABLE topicTable (
id int(5) NOT NULL AUTO_INCREMENT,
topicid char(3),
desc_short char(25),
desc_long text,
option0 char(100),
option1 char(100),
option2 char(100),
option3 char(100),
option4 char(100),
option5 char(100),
option6 char(100),
option7 char(100),
option8 char(100),
option9 char(100),
sex char(1),
college char(3),
class char(4),
campus char(1),
housing char(2),
key(id)
);

CREATE TABLE topic000Table (
id int(5) NOT NULL AUTO_INCREMENT,
userid int(5),
option0 char(1),
option1 char(1),
option2 char(1),
option3 char(1),
option4 char(1),
option5 char(1),
option6 char(1),
option7 char(1),
option8 char(1),
option9 char(1),
key(id)
);
 
Back
Top