Geek Answers Handbook

Database design, variable number of columns

I have a simple application in which users submit data to a table.

There are currently three fields that the user can pass to values ​​at the moment. I am trying to find a solution in which the number of description columns may vary depending on the description list created by the user.

So far I have been considering:

  • the presence of the user_input table has many values ​​with a zero field value, say, from 1 to 15 and thereby limiting the number of field descriptions that the user can define to 15. This solution is very easy to query and maintain, but is limited by a limited number of fields. (Is this a viable and acceptable solution in general?)
  • creating a table in which each row will match the 1 description entered. This will allow the user to create an unlimited number of description fields, however, storing each of all inputs will instead of 1 line now accept n-lines, where n is the number of linked links to the current list_ description. Users can select the sum of the columns, but they are not so simple to request and support.

My current table looks something like this:

CREATE TABLE `user_input` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `description_list_id` int(11) NOT NULL, `description1` int(11) NOT NULL, `description2` int(11) NOT NULL, `description3` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Are there any other solutions?

+7
sql mysql database-design
source share
2 answers

Definitely option number 2. Normalization is always the best option in such a scenario. You are right that this is more work, but you overcome the inevitable problem when you need more than 15 descriptions.

+11
source share

The second solution is preferable in terms of flexibility. If tomorrow you need to add additional description fields with the first solution, you will need to change the table and code to manage it.

The second solution may require a bit more work, but then it will process 2, like 200 descriptions.

The first approach is a quicker and dirtier solution for a small problem, the second is a good exercise if you have time to try something new.

+4
source share

More articles:


All Articles