Mysql – Database design for payment feature system

Architecturedatabase-designMySQL

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?

Best Answer

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.

Related Question