Best Practice to Use Multiple Parameters for SQL Column names?

Question

I have millions of items where I need to set whether they exist or not for many different configurations that will change over time.

I have four parameters.

1. Production
2. 14.15
3. Both-Local-General
4. Exists

I want to create a table where the column name looks like Production,14.15,Both-Local-General,Exists to store boolean values whether or not a certain item exists for that configuration. The items will each have one row.

The parameters will constantly change throughout time, so new columns will need to be added using a C# application.

I want to create a new column where the configuration will be different each time.

Is this a bad idea?

Should I store the parameters in a different table? Or is there a better way to do this?

Answer 1

This is a bad idea. Why not have a table called "Configurations" with two fields "Parameters" and "Value". Then Create/Update/Delete rows instead of columns. To do it the other way seems like a complete misuse of an RDBMS.

Answer 2

Its more efficient to create a table with a single column for the parameter names and use a Pivot or Case When to display it as you have mentioned when querying it.

Answer 3

You might be better of using a NoSQL solution like MongoDB or CouchDB. These solutions do not force a certain schema on your data which will alleviate the problem of modifying tables as your data continues to grow.

For example, in MongoDB you can have a collection where row 1 looks like:

{
  id: 1,
  production: myval,
  14.15: myval
}

and row 2 looks like:

{
  id: 2,
  production: myval,
  14.20: myval
}

There is no need to ever configure this data from the database side. You simply send it the json you want it to store and it stores it.

This is one of the many examples where NoSQL is perhaps a better solution for your problem.