Best way to design table to store attributes?

#1

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.

#2

carl.henthorn (AT) gmail (DOT) com wrote:

Quote:

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.

#3

On Jan 22, 12:53*pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

carl.henth... (AT) gmail (DOT) 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.- Hide quoted text -

- Show quoted text -

Thank you for bypassing the hazing of the rookie, I appreciate it
greatly! :-)
For the purposes of this table, my entity is a person, and the 150 bit
fields are a combination of columns describing all of the travel
interests that they may have in a "yes I am interested", or "No, I am
not interested" format. While it is possible to have a person
interested in all 150 travel destinations, it is very unlikely, thus
giving me a subset to work with. Since the set of "yes" values
determines which marketing pieces get sent out, I do need to keep
track of those, obviously, but I do not see the reason for keeping
track of the "no's" since marketing would not be sent to them anyways.
In my proposed table the absense of a row is the same as a "No" value.

Being moslty self taught, I was not familier with the term EAV. But
after looking it up, I can see that my issue is very simualr to a
patient-symptom set of data for a doctor. Sounds like this is the way
to go after all.

I still have to wonder if there is a better way, however, to store/
read that info. Perhaps a quasi-binary where each bit represents a
field in a footprint? or even the XML blob.
thank you again for the response!

#4

<carl.henthorn (AT) gmail (DOT) com> wrote

On Jan 22, 12:53 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

carl.henth... (AT) gmail (DOT) 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.- Hide quoted text -

- Show quoted text -

Thank you for bypassing the hazing of the rookie, I appreciate it
greatly! :-)
For the purposes of this table, my entity is a person, and the 150 bit
fields are a combination of columns describing all of the travel
interests that they may have in a "yes I am interested", or "No, I am
not interested" format. While it is possible to have a person
interested in all 150 travel destinations, it is very unlikely, thus
giving me a subset to work with. Since the set of "yes" values
determines which marketing pieces get sent out, I do need to keep
track of those, obviously, but I do not see the reason for keeping
track of the "no's" since marketing would not be sent to them anyways.
In my proposed table the absense of a row is the same as a "No" value.

Being moslty self taught, I was not familier with the term EAV. But
after looking it up, I can see that my issue is very simualr to a
patient-symptom set of data for a doctor. Sounds like this is the way
to go after all.

I still have to wonder if there is a better way, however, to store/
read that info. Perhaps a quasi-binary where each bit represents a
field in a footprint? or even the XML blob.
thank you again for the response!

Use three tables, one for persons, one for destinations, and one for
interests which maps persons to destinations. The interests table would
have one row {person, destination} for each destination that a person is
actually interested in.

#5

On Jan 22, 3:53 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

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.

I've often wondered about this line of thinking, that if your system
isn't implemented in a purely relational methodology, you have no
choice but to implement it as a state machine. However, I think that
I'm settling on the idea that its more the malfunction of the
relational advocate that they aren't able to handle situations outside
of their admittedly good relational foundations.

Just because the original design used what appears to be a perfectly
decent instance of repeating groups does not mean the designer is then
condemned to the complexity of Mr. Badour's imaginary zillion states.

While the DATA in these tables could have a zillion states, thats like
saying that an ssn field has 10^9 states because it has nine
characters that can range from zero to nine. Yes, there may be many
many possible values, but simple payroll programs for example are not
burdened with a zillion states as either entire ranges of values are
handled identically, or the unneeded values are simply never entered
into storage.

C'mon people, programming 101 here.

Does anybody have knowledge of the origin of this silly "state
machine" argument? I'm really curious why it carries any weight in
these sort of discussions but I also imagine that it originally was a
valid point offered by someone a bit more academically inclined than
Mr. Badour, and I'm genuinely interested in reading about it.

#6

On Jan 22, 7:18 pm, carl.henth... (AT) gmail (DOT) com wrote:

Quote:

On Jan 22, 12:53 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

carl.henth... (AT) gmail (DOT) 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.- Hide quoted text -

- Show quoted text -

Thank you for bypassing the hazing of the rookie, I appreciate it
greatly! :-)
For the purposes of this table, my entity is a person, and the 150 bit
fields are a combination of columns describing all of the travel
interests that they may have in a "yes I am interested", or "No, I am
not interested" format. While it is possible to have a person
interested in all 150 travel destinations, it is very unlikely, thus
giving me a subset to work with. Since the set of "yes" values
determines which marketing pieces get sent out, I do need to keep
track of those, obviously, but I do not see the reason for keeping
track of the "no's" since marketing would not be sent to them anyways.
In my proposed table the absense of a row is the same as a "No" value.

Being moslty self taught, I was not familier with the term EAV. But
after looking it up, I can see that my issue is very simualr to a
patient-symptom set of data for a doctor. Sounds like this is the way
to go after all.

I still have to wonder if there is a better way, however, to store/
read that info. Perhaps a quasi-binary where each bit represents a
field in a footprint? or even the XML blob.
thank you again for the response!

I would use repeating groups, or simply use a blob. I ran into the
same situation and had 150 or so "fields" with a range of a few values
(I went ahead and simple used a character position and it worked
fine).

You don't have to do everything in your database layer, and even if
you did, many sql implementations give you tools to programmatically
dissect the field to extract further values that could then be used in
sql queries themselves.

If all you have knowledge of is RDBMS tools then yes, you are going to
not do well with problem domains that aren't as well served by RDBMS
tools. But then again you don't see SQL specialists designing video
codecs either, so theres no real dilemma despite some of the noise I
see posted in this group protesting otherwise.

#7

patrick61z (AT) yahoo (DOT) com wrote:

Quote:

On Jan 22, 3:53 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
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.

I've often wondered about this line of thinking, that if your system
isn't implemented in a purely relational methodology, you have no
choice but to implement it as a state machine. However, I think that
I'm settling on the idea that its more the malfunction of the
relational advocate that they aren't able to handle situations outside
of their admittedly good relational foundations.

Just because the original design used what appears to be a perfectly
decent instance of repeating groups does not mean the designer is then
condemned to the complexity of Mr. Badour's imaginary zillion states.

While the DATA in these tables could have a zillion states, thats like
saying that an ssn field has 10^9 states because it has nine
characters that can range from zero to nine. Yes, there may be many
many possible values, but simple payroll programs for example are not
burdened with a zillion states as either entire ranges of values are
handled identically, or the unneeded values are simply never entered
into storage.

C'mon people, programming 101 here.

Does anybody have knowledge of the origin of this silly "state
machine" argument? I'm really curious why it carries any weight in
these sort of discussions but I also imagine that it originally was a
valid point offered by someone a bit more academically inclined than
Mr. Badour, and I'm genuinely interested in reading about it.i

Programming 101 has nothing to do with it. I doubt there is much 'genuine' interest because the record here shows precious few are interested enough to explore fundamentals and what you posted isn't 'academic' (as you say) at all, closer to the obfuscation that's so popular in db circles these days. From what I've seen, B. Badour, when he chooses to comment on incomplete questions, nearly always offers an indirection or two and at least one abstraction, which I find more valuable than direct instruction, being as indirections, for example, invite comparisons between equally valid design alternatives. In return for that advantage, one must apply much more thought and imagination than the frequently simplistic dictums and sloppy language so common here suggest (such as so-called 'relational methodology', there is no such thing, witness the big guns elsewhere who don't agree even about oft-supposed-solid theory like normalization). I'd say the point of the 'state machine' comp
arison was likely to illustrate that the original post completely lacked 'actionable' requirements, ie., the 'requirement', as stated, was silly (no offence intended to the OP who qualified his questions). I've seen people try to pass off such as real requirements more times than I want to remember. Note that the original poster hadn't clarified his requirement until later. A later message showed that he's trying to deal with a survey result. But since a marketing operation seems involved, there are further applications, eg., 'who bought a trip and might become a repeat customer?'.

(I think the 'dictums' are more or less okay when it comes to a specific products but c.d.t. is not about products.)

Besides, if 1.2 million respondents, times 150 questions, is considered a big db, I have to laugh. Only big to a mickey-mouse operation, I'd say. Those numbers might have been a prooblem with the hardware of thirty years ago, but not now, at least if the organization involved is serious about using a computer. Personally, if that's thought to be too big for accessible commercial hardware, I'd say it's likely that this is not an important problem. Given the cost of postage these days, any outfit that mails only half of the 150 possible offers to respondents without further analysis will soon be hamstrung with bigger problems, eg., bankruptcy. That company deserves to go broke for crappy analysis in the first place.

Note to newbies (even though most of you are semi-literate), don't take everything you see posted on c.d.t. at face value. Eg., don't assume that just because somebody uses a phrase like 'original design' that any real design exercise ever happened. Another free tip I have for you is at that in c.d.t. there is a never-ending supply of obfuscators who have no notion of the basics. Most posts here are from people who are familiar only with apps they've had something to do with, and are not really serious about theory. Try a little grade-school arithmetic to help compare posts you see here. Ask which sums look practical and which phrases seem nebulous, ie., impractical. Obviously when mail offers are involved, some business decision will be involved beyond any answers a db can provide. Obviously the computer could help identify the bell-curve, but you'd have to do a lot of googling to find a correlation between bell-curves and the relational model (or models plural if you l
ike, no doubt enough googling might suggest a weak-minded correlation, but that's another 'modern' problem). If you can't handle simple business arithmetic, you're probably wasting time here. Let alone mathematical philosophy, aka logic, which most of db theory revolves around.

#8

-As I recall, there is a standard code for destinations in the travel
industry, so you would have a two-column, 5NF table with (customer_id,
destination_code) as the key and name it something like Vacations.

#9

paul c wrote:

Quote:

patrick61z (AT) yahoo (DOT) com wrote:

On Jan 22, 3:53 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

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.

I've often wondered about this line of thinking, that if your system
isn't implemented in a purely relational methodology, you have no
choice but to implement it as a state machine.

What a stupid thing to say... regardless whether one implements the
state machine relationally or any other way, the state machine remains.

Quote:

Just because the original design used what appears to be a perfectly
decent instance of repeating groups does not mean the designer is then
condemned to the complexity of Mr. Badour's imaginary zillion states.

Who the fuck said anything about repeating groups? Apparently, Patrick
has dire problems comprehending relatively simple written english.
Independent variables are independent not repeating. Duh! ::rolls eyes::

Quote:

While the DATA in these tables could have a zillion states, thats like
saying that an ssn field has 10^9 states because it has nine
characters that can range from zero to nine.

Yes, exactly like that. One of my high school french teachers would have
reponded to that with "You have a fine grasp of the obvious." Except
elsewhere Patrick utterly fails to demonstrate any such grasp.

I suggest one stop to ponder that a billion seems like a large number
but it is infinitesimally small compared to 10^45, which is itself
infinitesimally small comparet to 10^90.

Quote:

Yes, there may be many
many possible values, but simple payroll programs for example are not
burdened with a zillion states as either entire ranges of values are
handled identically, or the unneeded values are simply never entered
into storage.

That depends on the payroll program. ADP regularly handles on the order
of 10^7 social security numbers, and the IRS handles on the order of
10^8 or 10^9 of them--when one stops and considers that even
non-resident aliens like myself sometimes have one.

Nevertheless, 10^45 states is a vast number of states. It is on the
order of the mass of the sun in picograms. However, Carl's explanation
suggests the first thing anyone will do with that data is dimension
reduction.

Quote:

C'mon people, programming 101 here.

Does anybody have knowledge of the origin of this silly "state
machine" argument?

Yes, of course, we all do. It originated in Patrick's skull as a figment
of his imagination. I doubt any argument exists beyond that scope.

Quote:

I'm really curious why it carries any weight in
these sort of discussions but I also imagine that it originally was a
valid point offered by someone a bit more academically inclined than
Mr. Badour, and I'm genuinely interested in reading about it.i

Since it carries no weight, the rest seems rather pointless.

<snip>

Quote:

I'd say the point of the 'state machine' comp-
arison was likely to illustrate that the original post completely lacked
'actionable' requirements, ie., the 'requirement', as stated, was silly
(no offence intended to the OP who qualified his questions).

I respectfully disagree. The point was to ask Carl to consider an
observation and to judge for himself whether his requirements are
reasonable in that light.

Quote:

I've seen
people try to pass off such as real requirements more times than I want
to remember. Note that the original poster hadn't clarified his
requirement until later. A later message showed that he's trying to
deal with a survey result. But since a marketing operation seems
involved, there are further applications, eg., 'who bought a trip and
might become a repeat customer?'.

I suspect those considerations don't impact on the design of the part of
the data model Carl asked about. Questions that come to my mind are: Why
booleans? Why not "likes", "dislikes" and "didn't answer" ? Or even a
numeric scale?

Can people take the survey more than once? Is the format of the survey
fixed for all time? Or might one use different versions of the survey at
different times? For example by adding or removing questions? Or by
persenting additional questions to some cohorts? If that is the case,
one might need another value like "not presented" as distinct from
"didn't answer".

Quote:

Besides, if 1.2 million respondents, times 150 questions, is considered
a big db, I have to laugh.

Hardware available in 1994 handled much larger databases quite well.
Capacities and speeds are much higher today.

150 dimensions seems unwieldy; except, I expect the data will be the raw
input for some sort of dimension reduction or regression analysis.

Carl seems concerned about the labor creating 150 columns in his table.
I don't see why it would involve any more labor than creating an
enumerated type of some sort with 150 values, which is what his
alternative designs seem to involve.

<snip>

#10

On Jan 24, 6:57 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

Those are big numbers, and it seems unlikely you really need such an
unwieldy state machine for each row of your table.

then I ask

Quote:

Does anybody have knowledge of the origin of this silly "state
machine" argument?

then he wrote

Quote:

Yes, of course, we all do. It originated in Patrick's skull as a figment
of his imagination. I doubt any argument exists beyond that scope.

Oh well, I'm not sure Bob's willing to educate me on this "state
machine" business. But I'm willing to read if anyone else wants to
dumb Bob's text down for me since I'm willing to accept that I'm so
dumb that Bob cannot stoop to my level. So what are we really talking
about here?

Quote:

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.

Anyone else? Why are we worried about state here? Carl, if you're
still reading, why are you writing a state machine? Or are you?