Re: Best way to design table to store attributes?

carl.henthorn_at_gmail.com wrote:

> 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.

Before folks start pointing and laughing and snickering about you re-inventing EAV yet again, I thought I would point out that the simplest logical representation for a boolean in a relational system is simply the existence of a tuple in a relation.

Thus, a design that has 150 tables each with a single column theoretically suffices.

On the other hand, such a design would clearly violate the POOD. Some might question the principle of orthogonal design, but I think one ignores it at one's own risk when creating 150 tables with identical predicates.

At a more basic level, though, are you sure you have correctly modelled your problem? 150 independent booleans creates a state machine with somewhere around 10^45 states. That's a big state machine. Without any sort of transition constraints, that creates a fully connected state machine with 10^45 states and somewhere around 10^90 allowable transitions.

Those are big numbers, and it seems unlikely you really need such an unwieldy state machine for each row of your table. Received on Thu Jan 22 2009 - 21:53:31 CET