I am designing a database for a game and the only option I can see a multiple table solution. Is there an option that I’m not seeing

database-design

I searched but could find a similar situation, if I missed it please let me know. In this game there are approx 14,000 items which have their own table. The players will want to build multiple collections of these items. The collections would generally range from 20 to 100 different items in varying quantities, but could have hundreds of different items. The problem comes with the storage of these collections. I can't create a collections tables with each row a collection because the number of fields varies so much.
I only see two solutions:

Create a collections table with a BLOB data field and store the data as a JSON object.
Give each collection its own table with an item ID field and a quantity field. Create a collection list table with the user id and the corresponding table name.

Is there a better option? If not, I am leaning toward option two so I can easily run statistic queries. Due to the number of tables that could exist would it benefit me to keep the collections in a database separate from my other tables?

Best Answer

You want to store an undefinied number of items in a list, for this you need a new table or cleaner two tables.

you already have the first table called items, each has an item_id.

Then one table holds all the references to the collections, so best case only a new collection_id for every of the collection.

And the important one is the MxN relation table between items and collections, let's call it collection_items, holding in each row one item_id and one collection_id.

If you want to have multiple same items in one collection, and only want to know how many, add a count field.

But if the relations need to be distinguishable, add a row for every relation and an extra collection_item_id.

Related Solutions

Questionnaire database design – which way is better

Definitely do not hard code your questionnaire. Use a relational database or xml files. I propose the following tables

Questionnaire: General description of questionnaire. Title, name of survey, questionnaire release date, version, and so on.
Section: The sections a questionnaire is made up. Number of the section, section title, description.
Question: The questions belonging to a section. Number of the question, question text, description, question type (text, multiple choice, etc.).
Question_Choice: The possible answers belonging to a question corresponding to the single checkboxes, radio buttons, and so on. Text of the choice, choice number, order.
Respondent: The persons answering the questions. Personal data, user number.
Interview: Interviews or tests or surveys (dependent on the nature of the questionnaire) belonging to one respondent and one questionnaire. If a respondent can always answer only one questionnaire (or if the survey is anonymous), this table is obsolete and can be merged with the Respondent table. Interview date (or test date or survey date), interviewer (if it applies).
Answer: Answers belonging to one interview (or respondent, see above) and one question. Answer text (for text type questions), choice (for radio buttons).
Answer_Choice: Choices belonging to one Answer and one Question_Choice when multiple choices can be checked.

This is a very normalized approach; however, you could decide to concatenate choices into one string or to store them as bit pattern or simplify it in some other way depending on your needs.

Mysql – How to design tables for players in a tournament

UPDATED version based on feedback:

CREATE TABLE group (
    group_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(team_id)
);

CREATE TABLE player (
    player_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    group_id INT,
    player_rank INT,
    PRIMARY KEY(player_id)
);

CREATE TABLE game (
    game_id INT NOT NULL AUTO_INCREMENT,
    match_date DATE,
    overtime TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (game_id)
);

CREATE TABLE player_game (
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_score INT,
    PRIMARY KEY (game_id, player_id)
);

...and UPDATED explanation: I think group and player are self-explanatory. The table game will have one record per match. The player_game table records the players in a match and the results; each match will have two records, one for each player.

Since you appear to be using InnoDB as the storage engine you may want to use foreign keys to enforce referential integrity, and obviously you can add other constraints and indexes as needed. If you need some functionality that this doesn't seem to cover, ask and I will either explain or add it.

Best Answer

Related Solutions

Questionnaire database design – which way is better

Mysql – How to design tables for players in a tournament

Related Question