Circular reference in table design?

[Ilan]

I have two alternative designs, and I would value your opinion as to which
is better.

The problem is the old one of bitches giving birth. I want to keep track of
all those animals born on the same birth date, to the same mother.

Two table option:

tblDog
ID [PK]
LitterFK [each dog is born to a litter]


tblLitter
DogFK
Date
ID (autonumber) [PK]


My problem with the above 2 table design is that it is 'circular'. You have
a one-to-many relationship from the dog table to the litter table (Dog.ID to
Litter.DogFK) and then a one-to-many relationship from the litter table to
the dog table (from Litter.ID to Dog.LitterFK). This somehow seems wrong,
but it may be fine.

I know that there is such a thing as a 'one-to-many self join' where tables
refer to themselves through a second table. However, I am not sure if this
is applicable here, because I would expect that in a self-join relationship,
the relationship would start and end with the same field (eg, Dog.ID relates
to LitterID, which relates back to Dog.ID, for examle). But in the
relationship proposed above DogID to Litter.DogFK then to Dog.LitterFK,
there is a 'gap' in the self join.



Three table option:

tblDog
ID
LitterFK

tblLitter
DogFK
Date
ID

tblBornToLitter
DogFK [PK]
LitterFK

Here I have a third table, 'BornToLitter' with a one-to-one relationship
with tblDog. One-2-one's are a bad thing, and normally indicate that one
table is not required. In this case, the tblBornToLitter is not required,
but only if it is OK to have a 'circular'

Please confirm that I am OK sticking to the two-table approach.
Many thanks

Ilan

 

[Ken Snell]

Here is what I'd recommend. This uses a junction table to relate the dogs
born in a litter to that litter.

tblDog
DogID (PK)
DogName
etc. (dog characteristics such as color, sex, etc.)

tblLitter
LitterID (PK)
DateOfBirth
MotherID (FK to DogID in tblDog)

tblLitterDog
LitterID (CPK) - FK to tblLitter
DogID (CPK) - FK to tblDog

 

[Tim Ferguson]

The problem is the old one of bitches giving birth. I want to keep
track of all those animals born on the same birth date, to the same
mother.

It seems to me that each litter could be identified by its mother and the
dateofbirth:

Dogs
====
DogNumber Primary Key
Mother FK references Dogs(DogNumber)
BirthDate
etc...

so that you could define a query on Litters like this:

SELECT Mother,
DATEVALUE(Birthdate) AS DateOfLitter,
COUNT(DogNumber) AS NumberOfPups
FROM Dogs
GROUP BY Mother, Birthdate

or access a particular litter like this:

SELECT DogNumber, etc
FROM Dogs
WHERE Mother = 1028
AND BirthDate = #1998-09-12#

Now the problem with this is when a litter may extend over midnight. If
this matters, you need an extra field to identify the litter

Dogs
====
DogNumber Primary Key
MotherNumber FK references Dogs
LitterDate // this is the date the litter began
BirthDate // not nec the same as LitterDate, see?
etc

You can, however, add a level of control by maintaining a separate table of
litters:

Litters
=======
MotherNumber FK references Dogs
LitterDate

Primary Key (MotherNumber, LitterDate)


The added advantage of this is that you would remove the FK constraint on
Dogs.MotherNumber and instead use

FK BelongsTo (MotherNumber, LitterDate) references Litters

This means that you cannot enter a Dogs.MotherNumber value that corresponds
to a male dog, or a bitch that has not had a litter. The disadvantage is
obviously a more fiddly user interface.

A good rule when you get mixed up in relationships is to stop worrying
about _what_ is connected to what, and start looking at _how_ they are
connected. If you can keep the meanings of relationships clear in your
head, then the mechanics fall simply into place. Use sensible names for
relationships that actually describe the real life situation. Always label
relationships when you are drawing them out on paper and use verbs to
describe them (and always use nouns to label tables).

For example, the relationship between Dogs and Litters is named BelongsTo,
as in "Dog(1099) BelongsTo Litter(1028, #1998-09-12#)" Even the most
complex database diagrams just degenerate into simple primary school
english lessons!



Hope that makes sense


Tim F

 

[Ilan]

Thank you for this.

I like the you give for using the expression 'belongs to' as a synonym to
'FK'. It is as useful as the expression 'is a' in inheritance. I shall use
it from now on.

If I understood correctly, you were at first suggestion that I do not need a
litter table at all. In the Dogs table, for each baby pup, I could name the
pup, its mother's name, the date of birth. Is this correct? The problem
with that is that my 'Litter' table contains many fields (eg, MotherBitch,
date, vetPresent, birthplace, etc) and I do not want to repeat all this data
for each pup.

Your second suggestion was that I could have a Litter table, and therefore
each pup will 'belong to' a litter. When you write: "A good rule when you
get mixed up in relationships is to stop worrying about _what_ is connected
to what, and start looking at _how_ they are connected" I believe you are
telling me that as long as I am happy with the idea that a puppy 'belongs'
to a litter, then I should not worry about the way I draw the
'relationships' in the Access relationship diagram.

As for having a litter table, you warn "The disadvantage is obviously a more
fiddly user interface." Right now I am fighting a losing battle with the
form designed to add puppies to litters. As I was banging my head against
the wall, I thought I better confirm with this newsgroup that my two table
approach was acceptable. You have assured me that it is (at least so I
think) so now I can go to the 'forms' newsgroup and hope they can help me
sort out the forms business.

Many thanks for your help.

 

[Ilan]

Here is what I'd recommend. This uses a junction table to relate the

dogs born in a litter to that litter.


DogID (PK)

etc. (dog characteristics such as color, sex, etc.)


LitterID (PK)

MotherID (FK to DogID in tblDog)


LitterID (CPK) - FK to tblLitter

DogID (CPK) - FK to tblDog

Now I have a problem. I ask the newsgroup 'should I have two tables or
three?'. And I get one reply 'well, you can only have one, but two is OK'
and I have your reply which says: 'use three'.

Three tables is more intuitive. But is it a better design? I originally
started with three tables, and then I had endless troubles with the forms
trying to add a 'litterdog'. So then I thought that maybe my troubles were a
result of a fault in my design, and that is when I realised that perhaps I
should only have two tables. I changed my design but still had endless
troubles with the forms.

You could tell me that Access input forms should not play any consideration
when I design my tables, and that the 'more robust' design will eventually
ensure easier data entry and retrieval.

So I guess the question is, what is a better design, two tables or three?
Now I am really not in a position to say which is better than the other. I
do know (from this newsgroup) that one-to-one relationships are to be
avoided (except in 'supertypes/subtypes'). In your design below, you have
the relationship between table Dog.DogID and table LitterDog.Dog Foreign
Key, and this is a one-2-one, right? Is that OK? Tim Ferguson just told
me 'stop worrying about _what_ is connected to what, and start looking at
_how_ they are connected'. And here I am worrying about what is connected
to what. But say I try to ask, how they are connected, using the
terminology 'belongs to'. A LitterDog does not 'belong to' a dog anymore
than a dog belongs to a 'litter dog'. This is getting a bit philosophical.

So tell me, if my prime concern is to get a form where the user will easily
be able to input dogs born in a litter, which approach do I go for?

Many thanks

Ilan

--

Ken Snell

<MS ACCESS MVP>

"Ilan" <ilan underscore sebba at btinternet stop com (e-mail address removed)>

wrote in message news:[email protected]...

I have two alternative designs, and I would value your opinion as to
which is better.

The problem is the old one of bitches giving birth. I want to keep
track

of

all those animals born on the same birth date, to the same mother.

Two table option:

tblDog
ID [PK]
LitterFK [each dog is born to a litter]


tblLitter
DogFK
Date
ID (autonumber) [PK]


My problem with the above 2 table design is that it is 'circular'.
You

have

a one-to-many relationship from the dog table to the litter table
(Dog.ID

to

Litter.DogFK) and then a one-to-many relationship from the litter
table to the dog table (from Litter.ID to Dog.LitterFK). This
somehow seems wrong, but it may be fine.

I know that there is such a thing as a 'one-to-many self join' where

tables

refer to themselves through a second table. However, I am not sure
if

this

is applicable here, because I would expect that in a self-join

relationship,

the relationship would start and end with the same field (eg, Dog.ID

relates

to LitterID, which relates back to Dog.ID, for examle). But in the
relationship proposed above DogID to Litter.DogFK then to
Dog.LitterFK, there is a 'gap' in the self join.



Three table option:

tblDog
ID
LitterFK

tblLitter
DogFK
Date
ID

tblBornToLitter
DogFK [PK]
LitterFK

Here I have a third table, 'BornToLitter' with a one-to-one
relationship with tblDog. One-2-one's are a bad thing, and normally
indicate that one table is not required. In this case, the
tblBornToLitter is not required, but only if it is OK to have a
'circular'

Please confirm that I am OK sticking to the two-table approach. Many
thanks

Ilan

Here is what I'd recommend. This uses a junction table to relate the dogs
born in a litter to that litter.

tblDog
DogID (PK)
DogName
etc. (dog characteristics such as color, sex, etc.)

tblLitter
LitterID (PK)
DateOfBirth
MotherID (FK to DogID in tblDog)

tblLitterDog
LitterID (CPK) - FK to tblLitter
DogID (CPK) - FK to tblDog


--
Ken Snell
<MS ACCESS MVP>

in message news:[email protected]...

I have two alternative designs, and I would value your opinion as to which
is better.

The problem is the old one of bitches giving birth. I want to keep

track

of
all those animals born on the same birth date, to the same mother.

Two table option:

tblDog
ID [PK]
LitterFK [each dog is born to a litter]


tblLitter
DogFK
Date
ID (autonumber) [PK]


My problem with the above 2 table design is that it is 'circular'. You have
a one-to-many relationship from the dog table to the litter table

(Dog.ID

to
Litter.DogFK) and then a one-to-many relationship from the litter table to
the dog table (from Litter.ID to Dog.LitterFK). This somehow seems wrong,
but it may be fine.

I know that there is such a thing as a 'one-to-many self join' where tables
refer to themselves through a second table. However, I am not sure if this
is applicable here, because I would expect that in a self-join relationship,
the relationship would start and end with the same field (eg, Dog.ID relates
to LitterID, which relates back to Dog.ID, for examle). But in the
relationship proposed above DogID to Litter.DogFK then to Dog.LitterFK,
there is a 'gap' in the self join.



Three table option:

tblDog
ID
LitterFK

tblLitter
DogFK
Date
ID

tblBornToLitter
DogFK [PK]
LitterFK

Here I have a third table, 'BornToLitter' with a one-to-one relationship
with tblDog. One-2-one's are a bad thing, and normally indicate that one
table is not required. In this case, the tblBornToLitter is not required,
but only if it is OK to have a 'circular'

Please confirm that I am OK sticking to the two-table approach.
Many thanks

Ilan

 

[Ken Snell]

In ACCESS, getting the table structure and relationships right is always the
first and important task. Once that's done correctly, the forms and reports
and queries, in most cases, will flow and work much easier. For some
situations, you may need to do a few fancy things with those other objects
in order to get info the way you want, but I never design my basic tables in
order that my forms will work a certain way.

If I find that I need a slightly different table for a form, I use a
temporary table to do that, and then delete that table after I'm done.

--
Ken Snell
<MS ACCESS MVP>

Here is what I'd recommend. This uses a junction table to relate the

dogs born in a litter to that litter.


DogID (PK)

etc. (dog characteristics such as color, sex, etc.)


LitterID (PK)

MotherID (FK to DogID in tblDog)


LitterID (CPK) - FK to tblLitter

DogID (CPK) - FK to tblDog

Now I have a problem. I ask the newsgroup 'should I have two tables or
three?'. And I get one reply 'well, you can only have one, but two is OK'
and I have your reply which says: 'use three'.

Three tables is more intuitive. But is it a better design? I originally
started with three tables, and then I had endless troubles with the forms
trying to add a 'litterdog'. So then I thought that maybe my troubles were a
result of a fault in my design, and that is when I realised that perhaps I
should only have two tables. I changed my design but still had endless
troubles with the forms.

You could tell me that Access input forms should not play any consideration
when I design my tables, and that the 'more robust' design will eventually
ensure easier data entry and retrieval.

So I guess the question is, what is a better design, two tables or three?
Now I am really not in a position to say which is better than the other. I
do know (from this newsgroup) that one-to-one relationships are to be
avoided (except in 'supertypes/subtypes'). In your design below, you have
the relationship between table Dog.DogID and table LitterDog.Dog Foreign
Key, and this is a one-2-one, right? Is that OK? Tim Ferguson just told
me 'stop worrying about _what_ is connected to what, and start looking at
_how_ they are connected'. And here I am worrying about what is connected
to what. But say I try to ask, how they are connected, using the
terminology 'belongs to'. A LitterDog does not 'belong to' a dog anymore
than a dog belongs to a 'litter dog'. This is getting a bit philosophical.

So tell me, if my prime concern is to get a form where the user will easily
be able to input dogs born in a litter, which approach do I go for?

Many thanks

Ilan

--

Ken Snell

<MS ACCESS MVP>

"Ilan" <ilan underscore sebba at btinternet stop com (e-mail address removed)>

wrote in message news:[email protected]...

I have two alternative designs, and I would value your opinion as to
which is better.

The problem is the old one of bitches giving birth. I want to keep
track

of

all those animals born on the same birth date, to the same mother.

Two table option:

tblDog
ID [PK]
LitterFK [each dog is born to a litter]


tblLitter
DogFK
Date
ID (autonumber) [PK]


My problem with the above 2 table design is that it is 'circular'.
You

have

a one-to-many relationship from the dog table to the litter table
(Dog.ID

to

Litter.DogFK) and then a one-to-many relationship from the litter
table to the dog table (from Litter.ID to Dog.LitterFK). This
somehow seems wrong, but it may be fine.

I know that there is such a thing as a 'one-to-many self join' where

tables

refer to themselves through a second table. However, I am not sure
if

this

is applicable here, because I would expect that in a self-join

relationship,

the relationship would start and end with the same field (eg, Dog.ID

relates

to LitterID, which relates back to Dog.ID, for examle). But in the
relationship proposed above DogID to Litter.DogFK then to
Dog.LitterFK, there is a 'gap' in the self join.



Three table option:

tblDog
ID
LitterFK

tblLitter
DogFK
Date
ID

tblBornToLitter
DogFK [PK]
LitterFK

Here I have a third table, 'BornToLitter' with a one-to-one
relationship with tblDog. One-2-one's are a bad thing, and normally
indicate that one table is not required. In this case, the
tblBornToLitter is not required, but only if it is OK to have a
'circular'

Please confirm that I am OK sticking to the two-table approach. Many
thanks

Ilan

Here is what I'd recommend. This uses a junction table to relate the dogs
born in a litter to that litter.

tblDog
DogID (PK)
DogName
etc. (dog characteristics such as color, sex, etc.)

tblLitter
LitterID (PK)
DateOfBirth
MotherID (FK to DogID in tblDog)

tblLitterDog
LitterID (CPK) - FK to tblLitter
DogID (CPK) - FK to tblDog


--
Ken Snell
<MS ACCESS MVP>

in message news:[email protected]...

I have two alternative designs, and I would value your opinion as to which
is better.

The problem is the old one of bitches giving birth. I want to keep

track

of
all those animals born on the same birth date, to the same mother.

Two table option:

tblDog
ID [PK]
LitterFK [each dog is born to a litter]


tblLitter
DogFK
Date
ID (autonumber) [PK]


My problem with the above 2 table design is that it is 'circular'.

You
have

a one-to-many relationship from the dog table to the litter table

(Dog.ID

to
Litter.DogFK) and then a one-to-many relationship from the litter

table

to

the dog table (from Litter.ID to Dog.LitterFK). This somehow seems wrong,
but it may be fine.

I know that there is such a thing as a 'one-to-many self join' where tables
refer to themselves through a second table. However, I am not sure if this
is applicable here, because I would expect that in a self-join relationship,
the relationship would start and end with the same field (eg, Dog.ID relates
to LitterID, which relates back to Dog.ID, for examle). But in the
relationship proposed above DogID to Litter.DogFK then to Dog.LitterFK,
there is a 'gap' in the self join.



Three table option:

tblDog
ID
LitterFK

tblLitter
DogFK
Date
ID

tblBornToLitter
DogFK [PK]
LitterFK

Here I have a third table, 'BornToLitter' with a one-to-one relationship
with tblDog. One-2-one's are a bad thing, and normally indicate that one
table is not required. In this case, the tblBornToLitter is not required,
but only if it is OK to have a 'circular'

Please confirm that I am OK sticking to the two-table approach.
Many thanks

Ilan

 

[Tim Ferguson]

If I understood correctly, you were at first suggestion that I do not
need a litter table at all. In the Dogs table, for each baby pup, I
could name the pup, its mother's name, the date of birth. Is this
correct?

It is correct as far as it goes. This should be enough to identify a
litter, but of course does not tell you anything about the litter...

The problem with that is that my 'Litter' table contains
many fields (eg, MotherBitch, date, vetPresent, birthplace, etc) and I
do not want to repeat all this data for each pup.

Quite right: this sentence says that Litters is an entity that has
attributes of its own, and therefore should be a table. MotherBitch is
presumably an FK referencing Dogs, and the two fields (MotherBitch,
DeliveryDate) would be the PK. The other two (and I presume there may be
others too) are genuine attributes (fields) in the table.

I believe you are telling me that as long as I am happy
with the idea that a puppy 'belongs' to a litter, then I should not
worry about the way I draw the 'relationships' in the Access
relationship diagram.

Not so much not to worry about, as that it all becomes obvious and easy.
Each pup has a FK BelongsTo (which has to match the PK of the Litters
table, so it's actually two fields (MotherBitch, DeliveryDate)) and the
rest is obvious [1].

Right now I am fighting a losing
battle with the form designed to add puppies to litters.

A typical solution would be to have a listbox that queries the litters
table, perhaps joined to the dogs table itself, that the user can pick a
litter from:

(1028) Diamond White 2003/03/19
(1028) Diamond White 2004/01/02
(1797) Dasher 2002/12/25
(2021) Grand Masha 2004/01/02

the brackets mean that you can make that column in the listbox invisible.
You would then use a little bit of code in the AfterUpdate event of the
list box to insert the MotherBitch and DeliveryDate into the appropriate
fieldsin the current Dogs record.

Another alternative would be to have a listbox of MotherBitches and co-
ordinate it with another listbox of DeliveryDates for that particular bitch
[2], but that takes a bit of coding too.

That's probably enough for now. Good luck!

[1] This looks tricky, and is one reason why some people advocate putting
an extra artificial key (e.g. LitterNumber) in every table to avoid having
two- or three-field FKs. My personal choice is usually to use the natural
keys, but it's very much you-pays-yer-money-and-you-takes-yer-pick.

[2] How many times can we get away with using the word "bitch" in this
thread without it getting spam-trapped? <g>


All the best


Tim F

 

[Ilan]

Thank you very much for your help.

I am assured that the two table design is the best approach. I have posted
a question in the forms newsgroup to help me with the forms, and the reply I
got was: 'you need three tables, and once you have three tables, here is
what you do'. I think for now I will be practical, and get the form
working, even with one table too many. At this stage, I think I have a
passable understanding of table design, but I ain't got a clue how forms
work. Once I can get the form working with three tables, I might feel bold
enough to modify things to work with the two table design. Anyway, I will
post a link to this thread, so they can take the design issue up with you.

Thanks once again.
Ilan

If I understood correctly, you were at first suggestion that I do not
need a litter table at all. In the Dogs table, for each baby pup, I
could name the pup, its mother's name, the date of birth. Is this
correct?

It is correct as far as it goes. This should be enough to identify a
litter, but of course does not tell you anything about the litter...

The problem with that is that my 'Litter' table contains
many fields (eg, MotherBitch, date, vetPresent, birthplace, etc) and I
do not want to repeat all this data for each pup.

Quite right: this sentence says that Litters is an entity that has
attributes of its own, and therefore should be a table. MotherBitch is
presumably an FK referencing Dogs, and the two fields (MotherBitch,
DeliveryDate) would be the PK. The other two (and I presume there may be
others too) are genuine attributes (fields) in the table.

I believe you are telling me that as long as I am happy
with the idea that a puppy 'belongs' to a litter, then I should not
worry about the way I draw the 'relationships' in the Access
relationship diagram.

Not so much not to worry about, as that it all becomes obvious and easy.
Each pup has a FK BelongsTo (which has to match the PK of the Litters
table, so it's actually two fields (MotherBitch, DeliveryDate)) and the
rest is obvious [1].

Right now I am fighting a losing
battle with the form designed to add puppies to litters.

A typical solution would be to have a listbox that queries the litters
table, perhaps joined to the dogs table itself, that the user can pick a
litter from:

(1028) Diamond White 2003/03/19
(1028) Diamond White 2004/01/02
(1797) Dasher 2002/12/25
(2021) Grand Masha 2004/01/02

the brackets mean that you can make that column in the listbox invisible.
You would then use a little bit of code in the AfterUpdate event of the
list box to insert the MotherBitch and DeliveryDate into the appropriate
fieldsin the current Dogs record.

Another alternative would be to have a listbox of MotherBitches and co-
ordinate it with another listbox of DeliveryDates for that particular bitch
[2], but that takes a bit of coding too.

That's probably enough for now. Good luck!

[1] This looks tricky, and is one reason why some people advocate putting
an extra artificial key (e.g. LitterNumber) in every table to avoid having
two- or three-field FKs. My personal choice is usually to use the natural
keys, but it's very much you-pays-yer-money-and-you-takes-yer-pick.

[2] How many times can we get away with using the word "bitch" in this
thread without it getting spam-trapped? <g>


All the best


Tim F

 

[Tim Ferguson]

I am assured that the two table design is the best approach. I have
posted a question in the forms newsgroup to help me with the forms,
and the reply I got was: 'you need three tables, and once you have
three tables, here is what you do'.

I have to confess that I don't really understand what the third entity
acheives, but that's the art of db design for you... How many litters can
one puppy belong to?

Best of luck anyway.

Tim F