Design relational

[Roger]

I have a simple data base with a Householder1 field and
Householder2 field and a field for duties that each
householder performs eg: Cooking, Washing etc. I have a
seperate table that lists all the duties. How do I
design a form or subforms where the Householders names
are listed and all the duties they perform. I am confused

Thanks in advance

 

[Jeff Boyce]

Roger

It sounds like the design you described uses a single field (duties
performed) to hold multiple facts (Cooking, Washing, ...). This is contrary
to good data design practices urging one fact-one field. In an instance
like you are describing, you have a one-to-many relationship. One
Householder can have zero, one or many "duties performed". You need a third
table to hold "duties performed by Householder.

This brings up a second potential issue. If your record holds two people
(Householder1 and Householder2), which one of these is the one performing
the duties?

A more normalized design would use one row/record per person, so you could
then use that person's ID in the "duties performed by Householder" table to
indicate who is doing it.

Is there a need for you to keep Householder1 and Householder2 associated
with each other? If so, you probably need a table to hold that
relationship.

 

[Roger]

Thanks Jeff for you help on this occasion and all the
advice you give to us novices over time. If I have a
person's ID field, does an ID have to be provided by
typing one in for each new person, or can it be
automatically generated?

Thanks again

-----Original Message-----
Roger

It sounds like the design you described uses a single field (duties
performed) to hold multiple facts (Cooking,

Washing, ...). This is contrary

 

[Jeff Boyce]

Roger

Take a look at the Autonumber data type in table definitions. An ID can be
automatically generated.

Now, do you want an ID number to relate a Householder to duties (in which
case no human really needs to see the ID #), or do you want/need an ID that
humans will use/see? If the latter, you'll need to create a sequence
numbering routine to assign IDs (and you could still use AutonumberIDs for
the row identifiers).

I'd suggest brushing up on relational database design and normalization to
help with this (data design) portion...

Good luck

Jeff Boyce
<Access MVP>