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

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

cardboard...fib er content, IsRecycledBool, etc
wood...species, etc
metal....gageSi ze, IsGalvanizedBoo l, 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)]

Re: One table or three?

Per MP:[color=blue]
>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....tblBoxe s
>
>but...each box type also has properties that only apply to that type...
>
>cardboard...fi ber content, IsRecycledBool, etc
>wood...species , etc
>metal....gageS ize, IsGalvanizedBoo l, 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[/color]

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]

Re: One table or three?

(PeteCresswell) wrote:[color=blue]
> Per MP:
>[color=green]
>>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....tblBoxe s
>>
>>but...each box type also has properties that only apply to that type...
>>
>>cardboard...f iber content, IsRecycledBool, etc
>>wood...specie s, etc
>>metal....gage Size, IsGalvanizedBoo l, 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[/color]
>
>
> 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.[/color]

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)]

Re: One table or three?

Per Stevel:[color=blue]
>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.[/color]

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@aol.com]

Re: One table or three?

Or, how about one table consisting of:
BoxID
Characteristic (lookup to list of possible characteristics such as
IsRecycledBool, etc
species, etc
gageSize, IsGalvanizedBoo l, 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@gmail.com]

Re: One table or three?

> is there any argument *against* lots of null values for "not applicable"[color=blue]
> fields?[/color]

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]

Re: One table or three?


"Stevel" <stevenlangenak en-at-@hotmail-dot-.com> wrote in message
news:1136472954 .523524@seven.k ulnet.kuleuven. ac.be...[color=blue]
> (PeteCresswell) wrote:[color=green]
> > Per MP:
> >[color=darkred]
> >>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....tblBoxe s
> >>
> >>but...each box type also has properties that only apply to that type...
> >>
> >>cardboard...f iber content, IsRecycledBool, etc
> >>wood...specie s, etc
> >>metal....gage Size, IsGalvanizedBoo l, 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[/color][/color][/color]
what[color=blue][color=green][color=darkred]
> >>properties to query???
> >>
> >>or should I have 3 tables tblCardboardBox , tblWoodBox, tblMetalBox[/color]
> >
> >
> > Or maybe even 4 tables:
> >
> > tblBox, which contains common info plus three RecordID fields - only one[/color][/color]
of[color=blue][color=green]
> > which is populated - and points to: tblCardboardBox , tblWoodBox, or
> > tblMetalBox.
> >
> > That way, you can query with three joins from tblBox and pick up[/color][/color]
whatever is[color=blue][color=green]
> > relevant, getting Null values for the tables in which there is no child[/color][/color]
rec.[color=blue]
>
> 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[/color]

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.fldB oxID
INNER JOIN tblBoxMetal
ON tblBox.fldBoxID = tblBoxMetal.fld BoxID

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)]

Re: One table or three?

Per MP:[color=blue]
>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...?[/color]

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

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]

Re: One table or three?

"(PeteCresswell )" <x@y.Invalid> wrote in message
news:n6pqr19rm9 cm4s2lrpk1c3632 a2hi21nvq@4ax.c om...[color=blue]
> Per MP:[color=green]
> >That would mean I don't need any code to determine before hand what type[/color][/color]
of[color=blue][color=green]
> >box it is...
> >since the boxId will only exist in one of the three subtype tables...?[/color]
>
> That's what moved me to do it that way - as opposed to a link table to a[/color]
single[color=blue]
> properties table as Penguin suggested. OTOH, Penguin's method is the[/color]
most[color=blue]
> open-ended, expansion-friendly of the two. You can properties until the[/color]
cows[color=blue]
> come home: all that's needed is a new record in tlkpBoxProperti es.
>
> I think mine is easier to code retrieval/presentation-wise, but I wouldn't[/color]
bet[color=blue]
> 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[/color]

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

Mark

ps, was the join idea even close???

[MP]

Re: One table or three?

<penguin732901@ aol.com> wrote in message
news:1136475913 .331630.45710@f 14g2000cwb.goog legroups.com...[color=blue]
> Or, how about one table consisting of:
> BoxID
> Characteristic (lookup to list of possible characteristics such as
> IsRecycledBool, etc
> species, etc
> gageSize, IsGalvanizedBoo l, 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...
>[/color]

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
fldBoxDescripti on

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

and then the table you showed above
tblBoxCharacter istics
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]

Re: One table or three?

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]

Re: One table or three?


"polite person" <sitting@ease.c om> wrote in message
news:17msr1dr4s r345mht5l5ur8au bj80116em@4ax.c om...[color=blue]
> a late response but just to say there is an example of this sort of thing[/color]
on the Access web[color=blue]
> http://www.mvps.org/access/resources/downloads.htm
> look for "one to one"[/color]


Thanks I'll check that out.