Search code examples
mysqldatabasedatabase-designrelational-databaseradix

How to store the specific (polls eg.) data in a MySQL database?

Let's say I would like to store votes to polls in mysql database.

As far as I know I have two options:

1. Create one table (let's say votes) with fields like poll_id, user_id, selected_option_id, vote_date and so on..

2. Create a new database for votes (let's say votes_base) and for each poll add a table to this base (a table, which consist the id of the poll in the name), let's say poll[id of the poll].

The problem with the first option is that the table will become big very soon. Let's say I have 1000 polls and each poll has 1000 votes - that's already a million records in the table. I don't know how much of the speed performance that will costs.

The problem with the second option is I'm not sure if this is the correct solution from the programming rules point of view. But I'm sure with this option it will be (much?) faster to find all votes to some poll.

Or maybe there is a better option?

Solution

  • Your first option is the better option. It is structurally more sound. Millions of rows in a table is no problem from MySQL. A new table per poll is an antipattern.

    EDIT for first comment:

    Even for a billion or more votes, MySQL should handle. Indexes are the key here. What is the difference between one database with 100 times the same table, or one table with 100 times the rows?

    Technically, the second option works as well. Sometimes it might be even better. But we frequently see this:

    • Instead of one table, users, with 10 columns
    • Make 100 tables, users_uk, users_us, ... depending on where the users are from.

    Great, no? Works, yes? Well it does, until you want to select all the male users, or join the users table onto another table. You'll have a huge UNION coming, and you won't even know the tables beforehand.

    One big users table, with the appropriate indexes, is better. If it gets too big for your liking (or your disk), you can start with PARTITIONING: you still have the benefit of one table, but the partitions are stored on different locations.

    Now, with your polls, these kind of queries might not happen. In that case, one big InnoDB table or 1000s of small tables might both work.. but the first option is a lot easier to program, and has no drawbacks over the second option. Why choose the second option?