Table Structure?: Null fields (vs) child table

[Clif McIrvin]

I'm working at normalizing a 'half-baked' application, and I've run into
a 'head scratcher'.

Many of the data entities that I work with have a number of 'optional'
fields. It's fairly easy to see logical groupings that can be moved to
child tables in a 1-1 relationship, which eliminates unused fields when
they come in a group .... but when one or a few of the related group of
optional fields are all that are used I seem to be back at the
beginning: an only partially populated record.

I could create an optional field table with four fields:

PK (autonumber)
FK (Long Int for autonumber value)
Field Type (Likely a pointer to a 'list' table)
Field Value

And then insert records only when the datum actually exists.

For instance, the Field Test data group includes:
Slump
Air Content
Sample Weight
Tare Weight
Sample Temperature
Ambient Temperature

and many times only one or two values are gathered and reported; other
times they all are.

Is it worth it to set up the 'Optional Data' table instead of just
accepting some non-trivial percentage of null values? (This is an
in-house app, with no formal support structure.)

Are there discussions of this concept that you can point me towards?

Comments?

Thanks in advance.

 

[Jeff Boyce]

Clif

I'm not sure I understand the underlying data yet...

Are you saying that an entity could have "(n)one to many" related
measurements (e.g., "Slump", "Air Content", ...)?

If that's an accurate paraphrase, then it sounds like you could get by with
a many-side table that holds:

EntityID (FK, pointing back to the entity)
MeasurementCategory (FK, pointing back to the table holding "Slump",
"Air Content", ... as individual rows)
Measurement (the value in the category)
UnitOfMeasure (probably pointing to a lookup table holding "Pounds",
"Inches", "...)

If your entity has no additional/optional measurements, there'd be no record
for it in this many-side table.

If you entity has 20 'optional' measurements, there'd be twenty rows with
that EntityID ...

.... Or maybe I'm still not seeing it...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clif McIrvin]

Clif

I'm not sure I understand the underlying data yet...

Are you saying that an entity could have "(n)one to many" related
measurements (e.g., "Slump", "Air Content", ...)?

If that's an accurate paraphrase, then it sounds like you could get by
with a many-side table that holds:

Yes, you grasped the fundamental concept.

Back when I started programming, no-one had ever heard of RDMS, and
after a decade or so out of the loop I'm finding it a challenge to get
my head wrapped around the concept of "tall, not wide" <grin>

Thanks for the feedback (and additional insight into flexibility - ie.,
unit of measurement).

 

[Jeff Boyce]

In my experience, "normal" isn't normal...<G>

Good luck

Jeff

 

[Clif McIrvin]

In my experience, "normal" isn't normal...<G>

Good luck

Jeff

Such encouraging words