Linking 4 child tables to 1 Parent table

[larpup]

I have a Parent table with a key field. I have 4 child tables. One
table is a many-to-one relation and the other three are one-to-one.
The three that are one-to-one may or may not have data entered into
them.

My question is; What is the best way to link these 4 tables to the
parent table?

The 4 child tables are all (really) related to the key field of the
parent table, but this has brought problems in underlying queries in
some forms. Appreciate any advice.

Lar

 

[Jeff Boyce]

First, if 3 tables are related one-to-one to the main (parent) table, why
are they separate tables? I'm not saying that they shouldn't be, I'm asking
what they contain that made you decide they need to be separate?

Next, the way to relate any child table to a parent table is to include a
field in the child table that will hold it's "parent's" ID/key -- this is
how it knows which parent it belongs to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[larpup]

Jeff,

Thanks for responding.

The reason the 3 tables are related is because the child table may or
may not have data entered into them (and there are several fields). The
ratio is about 50/50..

I've separated out the memo fields into a table as I've experience
corruption in the past and if there is corruption, they've only lost
the comments. Many records do not have comments.

I have a payable table with quite a few fields that again, may or may
not be populated.

I have another table that is a one-to-many (Parent/Child). This
obviously must remain separate.

Re: your last statement, this I am aware of.

So is it best to include the separated tables into my main table?
Lastly, I still need to link several child tables to the parent and
right now they are linked to the key field of the parent, so some
queries are not updateable.

Any advice will be greatly appreciated.

Lar

 

[Jeff Boyce]

There are situations in which keeping 1-1 "child" tables makes more sense.
It sounds like your situation may be one of these. Keeping those as
separate tables will probably be OK.

I'm not clear which queries you are referring to, but I do know that you can
build queries spanning 1-m table relationships and have them not be
updateable. Access HELP has more information about updateable queries...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[larpup]

Jeff,

Thanks again for replying. I still need to link several tables (child)
to one field in the (Parent) table...Is this acceptable?

Regards,

Lar

 

[John Vinson]

Thanks again for replying. I still need to link several tables (child)
to one field in the (Parent) table...Is this acceptable?

Acceptable, normal, even common.

John W. Vinson[MVP]

 

[chris.nebinger]

How many fields/records are we talking about? Blank fields take up
space, but not all that much. Depending on how many fields are in the
empty 1-1 tables, and how many rows there are, it might make more sense
to just include them in the main table. But, there are always
exceptions (255 field limit, for example)

 

[John Vinson]

How many fields/records are we talking about? Blank fields take up
space, but not all that much. Depending on how many fields are in the
empty 1-1 tables, and how many rows there are, it might make more sense
to just include them in the main table. But, there are always
exceptions (255 field limit, for example)

Well, not in a properly normalized database IME... there are very few
Entities one would put into a database with over 255 distinct types of
attributes! <g>

How to handle Subclassing (which this is) is tricky and often a
judgement call. You can indeed put the fields all into one table; this
can make for complicated record validation to ensure that the data in
each subclass is consistant within that subclass, and (if there are a
lot of fields) you can push the 1024 character limit of data actually
used in a record. Using one to one relationships to subclass tables
lets you have required fields in the "child" tables - i.e., if a
record exists in this table *at all*, it must have these fields filled
in. But there is additional system overhead for the additional tables,
indexes, relationships and so on, and more complexity in the form
design. Take your pick which you want - Scylla, or Charybdis!

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

"Lastly, I still need to link several child tables to the parent and
right now they are linked to the key field of the parent, so some
queries are not updateable."
Nothing in that statement would indicate that the query should not be
updatable. You'll need to post the query and define the relationships. It
is possible that your 1-1 relationships are defined backwards. Even a 1-1
relationship has one table which is the parent and another which is the
child. In a 1-1, the parent table will be the one with the autonumber
primary key.

When you have sparse 1-1 relationships or 1-m relationships, you need to use
Left joins in your queries.