Database for catologue

[Dr Pete]

I am on the committee of a dog club and want to produce catalogues for
dog shows and want to create a database of dogs and their parents, the
dogs owners and breeders and their addresses.

I would like to have a pull down list of dog names and owners/breeders
names.

The dogs could be both offspring and parent (obviously to different
dogs) and could have more than one Owner. What is the best wa of doing
this?

 

[Allen Browne]

All the dogs (parents and offspring) belong in the one table.
Self-join the table to build the pedigree relationships.
Example in:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

All the owners belong in one table (whether persons or companies.)

Then you will have a 3rd table to deal with ownerships. This table will have
fields:
DogID relates to a record in the Dog table.
OwnerID relates to a record in the Owner table.

That may be all you need, if you are happy to assume that if there are 2
owners, each one has a 50% stake. If that is not the case, add a 3rd field
(Number type, formatted as Percent) indicating the stake of the owner.

If you need to trace the history of ownership over time, then you would need
date fields as well:
StartDate the date this owner acquired this percentage stake in this
dog.
EndDate the date this owner relinquished this percentage stake in this
dog.

 

[Dr Pete]

Thanks for the reply. But I still need a bit more help if that's OK.
As The breeders may or may not the owners will this change things? Is
it possible to make the fields for dog, sire, dam or owner(s)
breeder(s) selectable from a pull down list (as in a combo box) and can
these lists be editiable?

Pete

 

[Allen Browne]

You can do that in the 3rd table I suggested.

Once you have those table in place, you will build a form bound to the Dog
table. The form will have a subform where you enter the owner/breeder
information (one entry per row.) The subform will have a combo where you
select the owner for that row.

You can't enter/edit the owners in that combo: there are just too many
fields that need to be considered, so it needs to be as separate form. You
could use the Double-click event of the combo to open the Owner form to
another owner can be entered if they are not already there.

 

[Dr Pete]

Many thanks for your help so far. this is my third attempt to reply to
the last bit of advice and so things have moved on bit but I'm now
stuck and bewildered.

I created the dog pedigree table succesfully and indeed have created
several others which include combo boxes.


I am not sure that I understand what you are saying in your last post.

Why do I need a subform and why can I have a combo box in that but not
the main form?

 

[Allen Browne]

If you have a main form for the dog, you cannot simply have a combo to
select the owner, because there could be more than one owner. You therefore
need a subform where you can enter many owners (one per row.)

If you have a main form for the owner/breeder, you cannot just a combo where
you select the dog, because one person can have many dogs. You therefore
need a subform when you can select the dogs this person owns.

One dog can have more than one owner/breeder.
One owner can have more than one dog.
You therefore have a many-to-many relation between people and dogs.
By adding a 3rd table (called a junction table), you can resolve the
many-to-many relation into a pair of one-to-many relations. This 3rd table
is the source for both the suforms described above.

 

[Dr Pete]

Again many thanks.

I feel like i am becoming bothconfused and bogged down so perhaps I can
go back a little and take things step by step. As I saod I've created
what I thought would be the most difficult part - the dog pedigree. As
for the "owners table" I have copied a table from another database
which I created some time ago. The third tabel you talk about I find
difficult to understand. What fields would you have in it and how
would it link?

Pete

 

[Allen Browne]

Dr Pete, I've explained this several times, so probably need to leave it
with you for now.

If you want to research if further, search for articles on "junction table",
"many to many", or "normalization".

All the best.

 

[Dr Pete]

Many thanks for all of your help, it has been much appreciated.
Perhaps somebody ele can now help me.

I have donelooked at junction tables and now understand the principle.
However, when I try and make the foreign key into one primary key I get
an error message saying that the primary key cannot have a null value.
Surely this is true for all Primary Keys and as such if I am using the
primary keys from two tables to make one primary key in the junction
table there should be no problem. So how do I solve this problem?