'Database design, variable number of columns [closed]

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

There are currently 3 fields the user can pass values to at the moment. I'm trying to find a solution where the number of description columns can vary, depending on the description list created by the user.

So far I have considered:

  1. having the user_input table have many nullable description fields, say from 1-15 and thus limiting the number of description fields the user can define to 15. This solution is very easy to query and maintain, however is limited to a set number of fields. (is this a viable and acceptable solution in general?)
  2. creating a table where each row would correspond to 1 description entered. This would allow the user to create unlimited number of description fields, however storing each all inputs would instead of 1 row now take n-rows, where n is the count of descriptions linked to the current description_list. Users are free to choose the amount of columns, however not so easy to query and maintain.

My current table is 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?

mysqlsqldatabase-design


Solution 1:[1]

Definitely option #2. Normalizaing is always the best option in a scenario like this. You are correct that it is more work, but you are overcoming the inevitable issue when you will need more than 15 descriptions.

Solution 2:[2]

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

The second solution can require a bit more work now but then it will handle 2 like 200 descriptions.

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

Solution 3:[3]

another option: You can have descriptions column with type as string. In this save | delimited description.

If there is use case to differentiate between different descriptions like one user can save value for description 4,7 then add one more column which save the column header.

Limitation : you wont be able to query on description directly but it can be handled on application side. I would suggest to use this if data is changes less frequently and query is not based on description.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Joe
Solution 2 mucio
Solution 3 techExplorer

Related Questions