Yes/No flags versus joins to subtables.

[GPO]

What is the best way (most efficient in terms of speed and
space) to attach a series of yes/no flags to records. For
example say I have a table that describes instances of
widgets (called tblWidgets). Each of these widgets is
characterised in part by the presence or absence certain
features. For example:

WidgetID: 1
WidgetName: Maxus 4000 Pro
IsInsured: Yes
IsInProduction: Yes
IsMadeInAustralia: No
ContainsCarcinogens: No
RunsOnDeisel: No
IncursSalesTax: No
IsUnderReview: No
..
..
..

WidgetID: 3
WidgetName: Minus 2000 Con
IsInsured: No
IsInProduction: Yes
IsMadeInAustralia: No
ContainsCarcinogens: Yes
RunsOnDeisel: Yes
IncursSalesTax: Yes
IsUnderReview: Yes
..
..
..

WidgetID: 5
WidgetName: Moonies 1000
IsInsured: No
IsInProduction: Yes
IsMadeInAustralia: Yes
ContainsCarcinogens: No
RunsOnDeisel: Yes
IncursSalesTax: Yes
IsUnderReview: No
..
..
..

Now I could just whack an extra column into tblWidgets
every time a new "Yes/No" flag is needed, but I'm
wondering instead whether a subtable for each flag might
be the answer. So in the above example I'd have
tblInsuredWidgets with one column (WidgetID) and the
identities of all the insured widgets in that column. By
joining the tables on WidgetID (one-to-one) it keeps the
main table more simple but are there other issues to
consider (maintenance, referential integrity etc)?

Or (getting adventurous) is a third solution to have a
table set up just for flags? It might have fields:

WidgetID (PK)
FlagType (PK)


The data might look like:
WidgetID FlagType
1 IsInsured
1 IsInProduction
3 IsInProduction
3 ContainsCarcinogens
3 RunsOnDeisel
..
..
..

This would solve the need for multiple tables AND the
problem of multiple columns in the main table.

Are there any other techniques that address this issue?

Regards

GPO

 

[John Vinson]

Now I could just whack an extra column into tblWidgets
every time a new "Yes/No" flag is needed, but I'm
wondering instead whether a subtable for each flag might
be the answer.

The redesign of your tables every time you need a new flag is the
glaring flaw in the first design.

So in the above example I'd have
tblInsuredWidgets with one column (WidgetID) and the
identities of all the insured widgets in that column. By
joining the tables on WidgetID (one-to-one) it keeps the
main table more simple but are there other issues to
consider (maintenance, referential integrity etc)?

A subtable for each flag is, if anything, even WORSE. Just for
starters, it's horribly space inefficient - each table has a lot of
overhead; and it stores data in tablenames, a serious no-no.

Or (getting adventurous) is a third solution to have a
table set up just for flags? It might have fields:

WidgetID (PK)
FlagType (PK)


The data might look like:
WidgetID FlagType
1 IsInsured
1 IsInProduction
3 IsInProduction
3 ContainsCarcinogens
3 RunsOnDeisel
.
.
.

This would solve the need for multiple tables AND the
problem of multiple columns in the main table.

Exactly. And that's why this type of "many to many resolver table" is
universally recommended for just this situation. Congratulations;
you've hit upon the correct solution!

Just one addition - you should have a table of FlagTypes related one
to many to this table as well, just to maintain relational integrity
and ensure that you don't have one widget which RunsOnDiesel and
another one which RunsOnDeisel. Only the first should be allowed.

 

[GPO]

Many thanks John! Much appreciated.

The redesign of your tables every time you need a new flag is the
glaring flaw in the first design.


A subtable for each flag is, if anything, even WORSE. Just for
starters, it's horribly space inefficient - each table has a lot of
overhead; and it stores data in tablenames, a serious no-no.


Exactly. And that's why this type of "many to many resolver table" is
universally recommended for just this situation. Congratulations;
you've hit upon the correct solution!

Just one addition - you should have a table of FlagTypes related one
to many to this table as well, just to maintain relational integrity
and ensure that you don't have one widget which RunsOnDiesel and
another one which RunsOnDeisel. Only the first should be allowed.