Need a good way to store data in a DB from a table on a webpage that can have N columns.

Question

Users need to be able to add a specific type of column to an otherwise static table on my web page and then save the information they enter in there to the database.

I've been told that in reality they will almost never go over 5 columns but I would rather support N. The columns will all be of the same datatype.

My first thought was to have an XML column with the values from all added columns in there but I was curious if anyone else had come up with a better solution. Suggestions?

Answer 1

if you serialize this data into one column (like using xml), you will have problems if you need to search on it.

if you don't ever need to search on it, then xml would be fine.

you could use parent-child model:

FixedTable
PK
data1
data2
...

VariableTable
PK - auto generate/identity
fk - points to FixedTable.PK
data1

and still have the ability to add an index and search. Your regualr data goes in the FixedTable, and any extra columns the users add, goes into the VariableTable.

Answer 2

You can use a vertical table. There are some hoops you have to jump through and you take some performance hits, but they're VERY flexible in this regards. Also, if you're using SQL Server, check out the PIVOT command.

Answer 3

If you must do this, do it in a separate table that you can later join to the original table. If you must, you can use an EAV structure, but be aware that the flexibility to add n columns has a very large performance hit. I'm talking really truly unacceptable performance in many cases and the more records the worse it gets. Do not go down this route unless you are sure you have no other choice.

Anothe route is to take them at their word and add a table with five columns labeled col1, col2, col3 (and whatever other columns you need to link to the user and/or other data table)and let them add data to those columns. If each user is going to name them something different, you might need a cross refernece table to use to figure out the column names.

You could put the data into a an xml field but how are you going to query this later? You really need to figure that out before you decide on a way to handle.

In general this level of flexibility is a poor idea. Five different users will each add five different columns that contain simliar data that you will want to query together but becasue they each used a different name, you have a mess instead of a database. If you have to have customer designed columns, try at least to get one admin per customer to do the adding of the columns, not users on the fly as they think of something nice to have.