Database design?

Question

I am trying to create a database for a dog kennel. This is what I have

Pet Table
PetID (Primary key), name, gender, breed, age, weight, CustID (Foreign Key) to Customer table

Customer Table
CustID (Primary Key), name, address, phone number.

Pen Table
PetID & CustID (Composite Primary Key)
PetID (Foreign Key to Customer Table)
Pen ID (Foreign Key to Pet table)

Apparently, my relationships are wrong, because the pen table is suppose to allow for more than one pet. Any help would be appericated.

Answer 1

Are you sure that you mean, on the Pen table that you have pen id as a foreign key to pet table? Isn't this the pet id. Similarly, you have pet id as a foreign key to customer table, this should be custid.
As you have custid on pet table as a foreign key, why do you need to have it concatenated with petid on the pen table? This is denomalising the data.

I would have Pet table exactly as you have it except I would add penid to this as a foreign key to pen table (Remember as a foreign key it has to have a value from the parent table or be null, so if the pet isn't currently in a pen, this column could be null)
Customer table exactly as you have it.
Pen table, pen id primary key .

Hope this helps. If I've got anything wrong you can email me.

Answer 2

I think the problem is in the Pen table. The Pen ID should be Primary Key and the PetID and Cust ID should be Foreign keys to the related tables.

Perhaps due to the fact that I'm not a born English speaker, I only understand the purpose of the Pet table and Customer Table. What is the Pen table for?

Makiavel

Answer 3

It's not clear what you want with this but, ill try to imagine what is what you want.

I guess your relations are

Pet (n) -------> (1) Customer

Pen(1) -------> (n) Pet

I am not sure if the Pen table needs a direct relation with the Customer table, but just with the Pet table.
So you would need only something like this:

Pet Table
PetID (Primary key),
name,
gender,
breed,
age,
weight,
CustID (Foreign Key) to Customer table
Pen ID (Foreign key to Pen Table) .... THIS is the field that will create the relation with the table PEN.

Customer Table
CustID (Primary Key),
name,
address,
phone number.

Pen Table
Pen ID (Primary Key)
Any other pen information

Answer 4

More than one Pet, of course, but only for a non-duplicate combination of Pet and Customer.