Member Avatar for hwoarang69

i creating a shopping website online for a store. and i wanted to add polls to get user input, than store in database.
i wasnt sure how to set up tables.

we going to have alost of questions. each question will have 3 to 4 answers. user can click on answers. onces the user has picked a answer than he/she cant not answer the same question again.

ex
question1: you farvite bread?
answer1: wheat
answer2: white
answer3: grain

question2 ...
answer1: ...
answer2:...

etc...

my idea to keep track of questions and how many times user click on answers:

question table:
-question_id
-user_id
-questions

answer table:
-answer_id
-question_id
-answer

issue iam having is that answer can be 3 or 5 different types.

  • 2 Contributors
  • 3 Replies
  • 118 Views
  • 38 Minutes Discussion Span
  • Latest Post Latest Post by hwoarang69

Dani AI

Generated

Short answer: model polls with three related tables — one for the poll (question), one for the options, and one for votes — and enforce “one vote per user” with a unique constraint or application logic. This keeps the design simple, handles any number of options per poll, and makes counting straightforward. wants a per-question vote cap (one vote per user); ’s note about MSSQL SQL_Variant isn’t needed here — options are typically text labels in MySQL (or JSON if you need richer option metadata) rather than mixed SQL types (MySQL JSON).

Suggested schema (normalized, InnoDB with FKs):

CREATE TABLE polls (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id BIGINT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  question TEXT NOT NULL,
  allow_multiple TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE poll_options (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  poll_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(255) NOT NULL,
  position SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE poll_votes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  poll_id BIGINT UNSIGNED NOT NULL,
  option_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  voter_token CHAR(36) NULL,      -- store cookie UUID for anonymous voters
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE,
  FOREIGN KEY (option_id) REFERENCES poll_options(id) ON DELETE CASCADE,
  UNIQUE KEY ux_poll_user (poll_id, user_id),
  UNIQUE KEY ux_poll_token (poll_id, voter_token)
) ENGINE=InnoDB;

Practical notes: require login and rely on ux_poll_user to forbid duplicates; for anonymous votes set a stable voter_token cookie and enforce ux_poll_token. Count votes with a grouped query (authoritative): SELECT option_id, COUNT(*) FROM poll_votes WHERE poll_id = ? GROUP BY option_id. Avoid relying on IP to block duplicates. For FK/transaction details and best practices, see the MySQL docs on foreign keys and InnoDB (MySQL CREATE TABLE — foreign keys) and the general normalization guidance (Database normalization).

Recommended Answers

All 3 Replies

Member Avatar for JorgeM

If you are referring to data types when referring to the answer, if you are using MSSQL, there is a data type called SQL Variant and its common for the use of storing different types of data.

Member Avatar for hwoarang69

--

Edited by hwoarang69
Member Avatar for hwoarang69

no i means how to struct tables.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.