Too many columns design question

Question

I have a design question.

I have to store approx 100 different attributes in a table which should be searchable also. So each attribute will be stored in its own column. The value of each attribute will always be less than 200, so I decided to use TINYINT as data type for each attribute.

Is it a good idea to create a table which will have approx 100 columns (Each of TINYINT)? What could be wrong in this design?

Or should I classify the attributes into some groups (Say 4 groups) and store them in 4 different tables (Each approx have 25 columns)

Or any other data storage technique I have to follow.

Just for example the table is Table1 and it has columns Column1,Column2 ... Column100 of each TINYINT data type.

Since size of each row is going to be very small, Is it OK to do what I explained above?

I just want to know the advantages/disadvantages of it.

If u think that it is not a good idea to have a table with 100 columns, then please suggest other alternatives.

Please note that I don;t want to store the information in composite form (e.g. few xml columns)

Thanks in advance

Answer 1

Wouldn't a many-to-many setup work here?

Say Table A would have a list of widget, which your attributes would apply to

Table B has your types of attributes (color, size, weight, etc), each as a different row (not column)

Table C has foreign keys to the widget id (Table A) and the attribute type (Table B) and then it actually has the attribute value

That way you don't have to change your table structure when you've got a new attribute to add, you simply add a new attribute type row to Table C

Answer 2

Its ok to have 100 columns. Why not? Just employ code generation to reduce handwriting of this columns.

Answer 3

I wouldn't worry much about the number of columns per se (unless you're stuck using some really terrible relational engine, in which case upgrading to a decent one would be my most hearty recommendation -- what engine[s] do you plan/need to support, btw?) but about the searchability thereby.

Does the table need to be efficiently searchable by the value of an attribute? If you need 100 indexes on that table, THAT might make insert and update operations slow -- how frequent are such modifications (vs reads to the table and especially searches on attribute values) and how important is their speed to you?

If you do "need it all" there just possibly may be no silver bullet of a "perfect" solution, just compromises among unpleasant alternatives -- more info is needed to weigh them. Are typical rows "sparse", i.e. mostly NULL with just a few of the 100 attributes "active" for any given row (just different subsets for each)? Is there (at least statistically) some correlation among groups of attributes (e.g. most of the time when attribute 12 is worth 93, attribute 41 will be worth 27 or 28 -- that sort of thing)?

Answer 4

BAsed on your last, it seems to me that you may have a bad design. WHat is the nature of these columns? Are you storing information together that shouldn't be together, are you storing information that shoul be in related tables?

So really what we need to best help you is to see what the nature of the data you have is.

what would be in
column1,column3,column10 vice column4,column15,column20,column25

Answer 5

I had a table with 250 columns. There's nothing wrong. For some cases, it's how it works.

unless some of the columns you are defining have a meaning "per se" as independent entities and they can be shared by multiple rows. In that case, it makes sense to normalize out the set of columns in a different table, and put a column in the original table (possibly with a foreign key constraint)

Answer 6

I think the correct way is to have a table that looks more like:

CREATE TABLE [dbo].[Settings](
    [key] [varchar](250) NOT NULL,
    [value] tinyint NOT NULL
) ON [PRIMARY]

Put an index on the key column. You can eventually make a page where the user can update the values.

Having done a lot of these in the real world, I don't understand why anyone would advocate having each variable be its own column. You have "approx 100 different attributes" so far, you don't think you are going to want to add and delete to this list? Every time you do it is a table change and a production release? You will not be able to build something to hand the maintenance off to a power user. Your reports are going to be hard-coded too? Things take off and you reach the max number of columns of 1,024 are you going to rework the whole thing?

It is nothing to expand the table above - add Category, LastEditDate, LastEditBy, IsActive, etc. or to create archiving functionality. Much more awkward to do this with the column based solution.

Performance is not going to be any different with this small amount of data, but to rely on the programmer to make and release a change every time the list changes is unworkable.