CODEWITHSUNDEEP
mysqldatabase-design
Apollo
Apollo

Reputation: 9064

Particular MySQL table structure

Could someone advise me on proper SQL architecture for this problem:

I am creating an app that has a dynamic number of options (option 1, option 2, option 3, etc...). I want the user to be able to select one of those options which will then increment a number associated with that option. How can I achieve a dynamic table, as I obviously can't pre-define 4 tables, for example, and have each be an option.

Thanks in advance.

Upvotes: 2

Views: 64

Answers (2)

dar7yl
dar7yl

Reputation: 3757

If you want to track which user selects which options, you could add a user-option table, and drop the occurrences counter from VoteyDeciple.

CREATE TABLE `UserOptions` (
    `uo_user` INT NOT NULL REFERENCES `users`(`user_id`),
    `uo_option` INT NOT NULL  REFERENCES `options`(`option_id`)
);

Then if you want to count occurrences of an option, you could use

SELECT count(*) as `occurrences` from UserOptions where uo_option={$option_id};

Upvotes: 0

VoteyDisciple
VoteyDisciple

Reputation: 37813

It sounds like you're looking for a table like

CREATE TABLE `options` (
    option_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    occurrences INT UNSIGNED
) ENGINE=InnoDB;

You can then run a query like:

UPDATE options SET occurrences = occurrences + 1 WHERE option_id = ____;

(Filling in the option_id based on what the visitor selected.)

Upvotes: 2

Related Questions