One-to-one Relationships

[dizneykids]

I have inherited a db with three tables, Adults, Youth and Persons. The first
two each have a one-to-one relationship with the third. I asked the designer
why the third, and was told that it was because Adults and Youth have name
and unit # in common (the adults are not necessarily the youth's parents,
though some may be). I'm trying to see the logic in having a 'master table'
listing all the people in the db, then having them connecting to the adults
and youth tables for more information.

Any thoughts?

 

[John W. Vinson]

I have inherited a db with three tables, Adults, Youth and Persons. The first
two each have a one-to-one relationship with the third. I asked the designer
why the third, and was told that it was because Adults and Youth have name
and unit # in common (the adults are not necessarily the youth's parents,
though some may be). I'm trying to see the logic in having a 'master table'
listing all the people in the db, then having them connecting to the adults
and youth tables for more information.

Any thoughts?

This is a technique called "Subclassing". It's often used when you have a type
of Entity (Persons in this case), all of whom have a common set of attributes
- all persons have last names, first names, birthdates, etc. This class of
Entities consists of two smaller sets - "subclasses". Each Youth is a Person,
but not all Persons are Youths (I'm certainly not... oh my aching back!); and
(depending on the nature of the application) Youths might have attributes that
pertain only to Youths and aren't relevant for adults, and vice versa. Just
hypothetically, one could say that a Youth might have a SchoolAttended and a
Grade, information that isn't relevant for adults; adults might have an
Employer, information not relevant for youths.

 

[dizneykids]

But is it necessary to set up a DB like this? Yes, everyone has a fname,
lname and phone number. But the majority of the youth will not have a
corresponding adult in the adult table. So, would it be so wrong to structure
the db with just the adult table and the youth table and not include the
linking persons table?

 

[John W. Vinson]

But is it necessary to set up a DB like this? Yes, everyone has a fname,
lname and phone number. But the majority of the youth will not have a
corresponding adult in the adult table. So, would it be so wrong to structure
the db with just the adult table and the youth table and not include the
linking persons table?

Well, you know your data and your problem space a lot better than I do. The
Subclassing has nothing to do with youth being linked to adults, though. It's
just a way to have one unique identifier for each person, regardless of
maturity, and to avoid wasted space for fields specific to only one subclass.

Of course it is NOT necessary; you can have separate tables if you wish. That
brings its own set of problems (e.g. having to use a UNION query if you want
to find addresses for everyone regardless of age) but it would certainly be
reasonable.

 

[dizneykids]

Thanks, John. I'm very new to Access and trying to feel my way around. I
appreciate your insight.

 

[Neil]

But is it necessary to set up a DB like this? Yes, everyone has a fname,
lname and phone number. But the majority of the youth will not have a
corresponding adult in the adult table. So, would it be so wrong to
structure
the db with just the adult table and the youth table and not include the
linking persons table?

I agree with what John said, but I would take a stronger aversion to using a
union query. While there's nothing wrong with union queries if appropriate,
if not necessary, they add a degree of cumbersomeness that you just want to
avoid. IMO it's far superior to have a single master table for looking up
all persons than to have to use a union query on the youth and adult tables
when you want to list all persons.

But I would flip it around. Rather than considering axing the master table,
I would consider axing the adult and youth tables. If the number of fields
in those tables are few in number, you could probably get by with incluing
all the youth and adult fields in the master table, with an additional field
signifying the type (Youth, Adult, and potentially any other types that you
may want to add later on).

While I wouldn't necessarily recommend merging the youth and adult tables
into the master table, if you want to simplify things I'd recommend it
before I'd recommend eliminating the master table and having your dataset in
two disjoined tables.

Best of all would be to leave it as-is unless you're intent on simplifying
things or, as noted, if there are only a couple of fields in the adult and
youth tables.

JMO*,

Neil
*JMO=Just My Opinion

 

[Jeanette Cunningham]

To add another thought to the mix:
What happens when a youth becomes an adult?
Is there a way of the database determining whether a person is a youth or
adult based on birthdate?

Jeanette Cunningham

 

[Neil]

To add another thought to the mix:
What happens when a youth becomes an adult?
Is there a way of the database determining whether a person is a youth or
adult based on birthdate?

I assumed that the db was for a little league or something like that, which
would have limited scope (a youth being in the db for a few years, and then
that's it). But if it's something that has a long range, then, yeah, that
might be a problem.

 

[Jeanette Cunningham]

There is always the possibility that a youth is on the border between youth
and adult at the time the database is put into use. We don't know the true
meanings of youth and adult in this context, or why it even matters for this
database.

Jeanette Cunningham

 

[Pat Hartman]

The Person table does not link the Youth table to the Adult table. It is a
superset that contains all members of each subset and includes only the
columns that both sets have in common.