Best way to design table to store attributes?
From: <carl.henthorn_at_gmail.com>
Date: Thu, 22 Jan 2009 12:35:07 -0800 (PST)
Message-ID: <d0e9d74c-897f-477a-89a3-77e8c2bc8ae6_at_t26g2000prh.googlegroups.com>
I have an entity that along with all of the usual attributes, has a set of 140 bit flag attributes that I need to track all yes/no values of. I started going down the path of creating a table 150 columns wide, but after typing a few of them thought there may be a better way. :-)
I am thinking of creating a table like this: Create table details (
DetailsID int identity(1000,1),
Name nvarchar(40),
Descr nvarchar(60),
Category nvarchar(40) )
Thus I can take what would have been my column headers and use them in Name field, throw what would have been the bit flag value and throw that into the description field. I would tie them together with a category value to combine them into logical groups. I have around 1.2 million entities to store this info for, so this new table will get long fast.
Date: Thu, 22 Jan 2009 12:35:07 -0800 (PST)
Message-ID: <d0e9d74c-897f-477a-89a3-77e8c2bc8ae6_at_t26g2000prh.googlegroups.com>
I have an entity that along with all of the usual attributes, has a set of 140 bit flag attributes that I need to track all yes/no values of. I started going down the path of creating a table 150 columns wide, but after typing a few of them thought there may be a better way. :-)
I am thinking of creating a table like this: Create table details (
DetailsID int identity(1000,1),
Name nvarchar(40),
Descr nvarchar(60),
Category nvarchar(40) )
Thus I can take what would have been my column headers and use them in Name field, throw what would have been the bit flag value and throw that into the description field. I would tie them together with a category value to combine them into logical groups. I have around 1.2 million entities to store this info for, so this new table will get long fast.
Another passing thought was to store these sets of values as XML for each entity. I have to admit I dont know what the advantage is for that other than the row count savings. how easy would it be to search through a 140 field xml blob for 1 million entites?
Any advice is appreciated on how to tackle this. Received on Thu Jan 22 2009 - 21:35:07 CET