Design problem

[Vaderman]

I have 3 tables.. For example (and this is just an example, but the solution will be the same.) Person and Animal and they both share a commonality in that they have personal details like Name, Age, Sex (call this PersonalDetails table).

At the moment, the relationship stands as being 1:1 Person<->PersonalDetails and 1:1 Animal<->PersonalDetails. as 1 entity whether albeit a person or animal can only have one set of details.

Now, what I'm looking for is how to describe in a Relationship that it can either be an Animal or a Person, when they enter the PersonalDetails info as they cannot be both.

At the moment, if I enter details about the Person, I'm forced to enter details about Animal and vice versa when, in fact, I just want to enter information about the Animal OR the Person. Ideally, they should be mutually exclusive of each other.

To hopefully make it clearer, at design stage the tables Person and Animal are disjointed tables that both use the PersonalDetails table, but the PersonalDetails table has no information regarding the type, i.e 'the information I have on PersonalDetails, is it an Animal or Person?'.

It is this that I'm having problems with: How do you implement a disjointed tables relation in Access?

I hope that makes sense.

Regards

John

[hspc]

At the moment, if I enter details about the Person, I'm forced to enter details about Animal and vice versa when, in fact, I just want to enter information about the Animal OR the Person.Seems that you make PersonalDetails reference Person and Animal ..
But it should be like this :
PersonalDetails has a PK
Person has a PK that references PersonalDetails's PK
Animal has a PK that references PersonalDetails's PK

[Vaderman]

Thanx for the reply hspc. The integrity constraints that you described I already have in place :


PersonalDetails

PersonalDetails_ID
Name
Sex
Age

Person
PersonalDetails_ID
Address1
Address2
etc

Animal
PersoanlDetails_ID
AnimalType
etc
etc


If I enter persons details I have to enter Animal details too, and that is not what I really wanted from the design. Really wanted the tables to be disjoint in that it forces the user to enter data for Animal or Person.

Have I missed anything?

John

[Chris_Student]

The person and animal tables should have their own primary keys. Note that PersonalDetails_ID is only a primary key for the PersonalDetails table, and is only a field in the other two tables.

I don't see why you would have to add anything to the Animal table if you just want to add a Person.

For example. Add a record to the PersonalDetails table with PersonalDetails_ID = 7, and add a record to the Person table with PersonalDetails_ID = 7. There should also be a primary key for the Person table. For example Person_ID = 3. Then, there should be no record in the animal table with PersonalDetails_ID = 7.