One table or three?

[MP]

context: (vb6 / ado / .mdb / jet4.0 / not using access)

hypothetical problem
say I need to track the properties of boxes.

There are three kinds, cardboard, wood, and sheet metal.

Each box has some common properties, width, depth, height
So one table makes sense to store that....tblBoxes

but...each box type also has properties that only apply to that type...

cardboard...fiber content, IsRecycledBool, etc
wood...species, etc
metal....gageSize, IsGalvanizedBool, etc

so if I have one table, each entry will have lots of nulls for the
properties that don't apply,
and the calling code will have some kind of branching code to decide what
properties to query???

or should I have 3 tables tblCardboardBox, tblWoodBox, tblMetalBox
then the calling code still needs branching code to decide what table to
query but there aren't a bunch of nulls...

is there any argument *against* lots of null values for "not applicable"
fields?
is it easier to make branching code to access different fields versus
different tables?
any suggestions?
Thanks
Mark

[(PeteCresswell)]

Per MP:

There are three kinds, cardboard, wood, and sheet metal.

Each box has some common properties, width, depth, height
So one table makes sense to store that....tblBoxes

but...each box type also has properties that only apply to that type...

cardboard...fiber content, IsRecycledBool, etc
wood...species, etc
metal....gageSize, IsGalvanizedBool, etc

so if I have one table, each entry will have lots of nulls for the
properties that don't apply,
and the calling code will have some kind of branching code to decide what
properties to query???

or should I have 3 tables tblCardboardBox, tblWoodBox, tblMetalBox

Or maybe even 4 tables:

tblBox, which contains common info plus three RecordID fields - only one of
which is populated - and points to: tblCardboardBox, tblWoodBox, or
tblMetalBox.

That way, you can query with three joins from tblBox and pick up whatever is
relevant, getting Null values for the tables in which there is no child rec.
--
PeteCresswell

[Stevel]

(PeteCresswell) wrote:

Per MP:

There are three kinds, cardboard, wood, and sheet metal.

Each box has some common properties, width, depth, height
So one table makes sense to store that....tblBoxes

but...each box type also has properties that only apply to that type...

cardboard...fiber content, IsRecycledBool, etc
wood...species, etc
metal....gageSize, IsGalvanizedBool, etc

so if I have one table, each entry will have lots of nulls for the
properties that don't apply,
and the calling code will have some kind of branching code to decide what
properties to query???

or should I have 3 tables tblCardboardBox, tblWoodBox, tblMetalBox

Or maybe even 4 tables:

tblBox, which contains common info plus three RecordID fields - only one of
which is populated - and points to: tblCardboardBox, tblWoodBox, or
tblMetalBox.

That way, you can query with three joins from tblBox and pick up whatever is
relevant, getting Null values for the tables in which there is no child rec.

I'd suggest a record ID, the type (Cardboard, Wood or Metal) and the
common fields in one table, the record ID and the proprietary fields in
three other tables.

Stevel

[(PeteCresswell)]

Per Stevel:

I'd suggest a record ID, the type (Cardboard, Wood or Metal) and the
common fields in one table, the record ID and the proprietary fields in
three other tables.

But how would he join to the other three tables simultaneously? Seems like
that would introduce the need for some logic before doing the join.
--
PeteCresswell

[penguin732901]

Or, how about one table consisting of:
BoxID
Characteristic (lookup to list of possible characteristics such as
IsRecycledBool, etc
species, etc
gageSize, IsGalvanizedBool, etc

and Value e.g.
BoxID Characteristic Value
124 isrecycled T
128 gagesize 12
128 isgalvanized T

I'm not saying it'd be _easy_ to query, but with crosstabs, maybe...

[jeremygetsmail]

> is there any argument *against* lots of null values for "not applicable"

fields?

Not really. Disk space is nearly free. Records don't actually grow much
with blank fields. I don't know about performance, but if you're
talking about several fields for each type, as opposed to several dozen
fields for each type, I can't imagine there's a measurable performance
hit, and certainly not one that wouldn't be present in many other
queries.

I say put it all in one table.

Jeremy

[MP]

"Stevel" <[e-mail]> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ...

(PeteCresswell) wrote:

Per MP:

There are three kinds, cardboard, wood, and sheet metal.

Each box has some common properties, width, depth, height
So one table makes sense to store that....tblBoxes

but...each box type also has properties that only apply to that type...

cardboard...fiber content, IsRecycledBool, etc
wood...species, etc
metal....gageSize, IsGalvanizedBool, etc

so if I have one table, each entry will have lots of nulls for the
properties that don't apply,
and the calling code will have some kind of branching code to decide whatproperties to query???

or should I have 3 tables tblCardboardBox, tblWoodBox, tblMetalBox

Or maybe even 4 tables:

tblBox, which contains common info plus three RecordID fields - only one of which is populated - and points to: tblCardboardBox, tblWoodBox, or
tblMetalBox.

That way, you can query with three joins from tblBox and pick up whatever is relevant, getting Null values for the tables in which there is no child

rec.
I'd suggest a record ID, the type (Cardboard, Wood or Metal) and the
common fields in one table, the record ID and the proprietary fields in
three other tables.

Stevel

so maybe 5 tables?
....
common box table
tblBox
fldBoxID PK
fldBoxTypeID FK from tblBoxType
....then fields common to all box types
fldBoxWidth
fldBoxHeight
fldBoxDepth
fldBoxName
....
"lookup table" for types
tblBoxType
fldBoxTypeID PK
fldBoxTypeName (one record for each type)
....
tblBoxCardboard
fldBoxId FK from tblBox
fldBoxFiber
....
tblBoxWood
fldBoxId FK from tblBox
fldBoxSpecies
....
tblBoxMetal
fldBoxId FK from tblBox
fldBoxGage

then to get info on one specific box
(mind you this is my first attempt to learn sql so not sure about these JOIN
clauses)
would this automatically get the records for a particular box regardless of
what type it is?

Select * From tblBox
From tblBox INNER JOIN tblBoxCardboard
ON tblBox.fldBoxID = tblBoxCardboard.fldBoxID
INNER JOIN tblBoxWood
ON tblBox.fldBoxID = tblBoxWood.fldBoxID
INNER JOIN tblBoxMetal
ON tblBox.fldBoxID = tblBoxMetal.fldBoxID

do I understand join correctly that if the fldBoxID does not exist in that
particular table, then no records will be returned.
That would mean I don't need any code to determine before hand what type of
box it is...
since the boxId will only exist in one of the three subtype tables...?

thanks
Mark

[(PeteCresswell)]

Per MP:

That would mean I don't need any code to determine before hand what type of
box it is...
since the boxId will only exist in one of the three subtype tables...?

That's what moved me to do it that way - as opposed to a link table to a single
properties table as Penguin suggested. OTOH, Penguin's method is the most
open-ended, expansion-friendly of the two. You can properties until the cows
come home: all that's needed is a new record in tlkpBoxProperties.

I think mine is easier to code retrieval/presentation-wise, but I wouldn't bet
the mortgage money on that without a little fooling around with skeleton
implementations of each approach.

tlkpBoxType is definitely needed - an omission by Yours Truly.
--
PeteCresswell

[MP]

"(PeteCresswell)" <[e-mail]> wrote in message
news:n6********************************@4ax.com...

Per MP:

That would mean I don't need any code to determine before hand what type ofbox it is...
since the boxId will only exist in one of the three subtype tables...?
That's what moved me to do it that way - as opposed to a link table to a

single properties table as Penguin suggested. OTOH, Penguin's method is the most open-ended, expansion-friendly of the two. You can properties until the cows come home: all that's needed is a new record in tlkpBoxProperties.

I think mine is easier to code retrieval/presentation-wise, but I wouldn't bet the mortgage money on that without a little fooling around with skeleton
implementations of each approach.

tlkpBoxType is definitely needed - an omission by Yours Truly.
--
PeteCresswell

Thanks, I'll study Penguins idea some more to see if I can Grok it!
:-)

Mark

ps, was the join idea even close???

[MP]

<pe***********@aol.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Or, how about one table consisting of:
BoxID
Characteristic (lookup to list of possible characteristics such as
IsRecycledBool, etc
species, etc
gageSize, IsGalvanizedBool, etc

and Value e.g.
BoxID Characteristic Value
124 isrecycled T
128 gagesize 12
128 isgalvanized T

I'm not saying it'd be _easy_ to query, but with crosstabs, maybe...

Thanks for the response, Penguin

I'm trying to understand it

when you say "BoxID" I thought you meant that would be the PK for tblBox
but since there's more than one entry it can't be.
so the PK would be another field not shown above...is that correct?

if that's the correct interpretation, wouldn't that mean I have another
table to store BoxID as PK?
tblBox
fldBoxID PK
fldBoxDescription

when you say "lookup to list of..." are you referring to a "Lookup table"?
if so, wouldn't I have another table tblCharacteristics?
tblChar
fldCharID PK
fldCharDescription

and then the table you showed above
tblBoxCharacteristics
fldBCID PK
fldBoxID FK from tblBox
fldCharID FK from tblChar
fldCharVal - with constraints to enforce buisness rules????

then say one type of box has 10 characteristics, I would have 10 entries
with same BoxID
is that what you're suggesting???

I'm not sure that's what you were saying because then I'm back to 3 tables.

sorry, I guess I'm not fully understanding the idea you're trying to convey
about only one table.
Can you set me straight?

Thanks
Mark

[polite person]

a late response but just to say there is an example of this sort of thing on the Access web
http://www.mvps.org/access/resources/downloads.htm
look for "one to one"

[MP]

"polite person" <si*****@ease.com> wrote in message
news:17********************************@4ax.com...

a late response but just to say there is an example of this sort of thing on the Access web http://www.mvps.org/access/resources/downloads.htm
look for "one to one"

Thanks I'll check that out.