0

I am building a system where visitors can buy modules from my website. For now I only have 3 available, but of course this number will keep growing.

In order to facilitate this in my MySQL database I am doubting between one of these two architectures, mainly concerning about performance and scalability.

I have a user table in my solution which consists of the user_id, email, password and other fields.

Approach one: One table called "user_modules" where a column represents each module, and a boolean (1 or 0) corresponds to either they have the module or not.

+---------+----------+----------+----------+
| user_id | module_1 | Module_2 | Module_3 |
+---------+----------+----------+----------+
|       1 |        1 |        0 |        1 |
|       2 |        1 |        1 |        0 |
|       3 |        1 |        1 |        1 |
+---------+----------+----------+----------+

Approach two: Two tables: a modules table where all the different modules are in

+-----------+-------------+--------------------+-------+
| module_id | module_name | module_description | price |
+-----------+-------------+--------------------+-------+
|         1 | test        | test2              |     5 |
|         2 | demo        | demo               |    10 |
+-----------+-------------+--------------------+-------+

user_modules table where every row is a user_id coupled with a module_id.

+---------+-----------+
| user_id | module_id |
+---------+-----------+
|       1 |         1 |
|       1 |         2 |
|       2 |         3 |
|       2 |         1 |
|       2 |         5 |
|       3 |         1 |
+---------+-----------+

Now that I typed everything out, it seems clear to me that approach two is the better option. However, are there other suggestions or other possible improvements here?

3

The second approach conforms to Third Normal Form (3NF) which is always a better place to start than something which is un-normalized (0NF) like your first option.

Converting repeating columns into rows in another table is perfectly scalable and will help keep your source code more stable because you won't be adding columns to your (option 1) table when you create new modules.

Furthermore, the second option is more powerful because it lets you easily add other information about the purchase of a module by a particular user. For example you could add purchase_date to your user/module table. You could also add the actual_purchase_price to that same table. This would allow you to keep track of things like changes to the price you charge for modules or whether you sold a particular module to a particular user at a promotional price.

1

I'd go with the second approach. That way you can add more modules in the future more easily as you just have to add a new entry in the module table. Whereas, if you go with the first approach and you want to add a new module, you'd have to create a new table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.