E/R Diagram help for Pet Database

[maccessgirl]

Greetings,

I posted a yesterday about help for a database I am working on. Well I
sat down and came up with some entities and attributes based on my
wishes and came up with the below. I'm going to list the criteria again

Here is the situation. I need to create a animal shelter database on
the following criteria.

· Track those who adopt pets from shelter so that "thank you
" letters can be sent

· Track how many pets are adopted during any period of time,
such as by month or year

· Track how many animals are adopted by the same family. The
shelter only allows adoption if three months have passed from any
previous adoption

· Manage and remind for vaccinations, surgery and medical
treatments

· Store owners desired criteria and automatically be notified
when suitable animals arrive

Here are the entities that I have come up with:
Pets
ID
Breed
Type
DOB
Gender
Color
Adoption Date

Family
Name
ID
Customer Type (Individual, Store, Institution)
Street
City
State
Zip Code
Date of Adoption
1 or more pets

Desired Criteria
Animal
Age
Gender
Color
Current Vaccinations
Neutered/ Sprayed

Medical Information
Vaccination ID
Surgery ID
Medical Treatment ID
Name
Date

This is what I cam up with. Please critique and correct me to the hilt
on what I did wrong. Something tells me that I have a lot of
redundancy.

Also, What would be the primary key... foreign key

Thanks in advance,
MACCESSGIRL

 

[jahoobob via AccessMonster.com]

I would add FamilyID as a Foreign Key to the Pets table so you can keep track
of who adopts what pet. You don't need date of adoption in the Family table
since it is in the Pets table and the adoption date can be obtained since the
Family ID is in the Pets table. You also don't need 1 or more pets in the
Family table since you can count the number of pets adopted by one family in
the Pets table.
Also, for all the field names, don't leave spaces so that Date of Adoption
should be written either Date_of_Adoption or DateofAdoption.
If your Medical information table is for storing treatments, vaccinations,
etc. for a pet then add the Pet's ID as an FK not the Name.
For the desired criteria add the famil ID as an FK. That way you can have
many criteria for one family e.g. they want a Lab and/or a Terrier.
You will need more tables. One with a list of Surgeries, one with a list of
vaccinations, and one with a list of treatments all with ID numbers that you
will store in the medical information table.
Also, never use Date (a reserved word) as a field so change date in
MedicalTreatments to MedDate
Your tables will look like this:

Pets
PetID -PK
FamilyID -FK
Breed
Type
DOB
Gender
Color
AdoptionDate

Family
Name
FamilyID -PK
CustomerType (Individual, Store, Institution)
Street
City
State
ZipCode


DesiredCriteria
CriteiaID -PK
FamilyID - FK
Animal
Age
Gender
Color
Current Vaccinations
NeuteredSprayed

MedicalInformation
MedID -PK
PetID - FK
VaccinationID -FK
SurgeryID -FK
MedicalTreatmentID -FK
MedDate

Treatments
TreatmentID -PK
Treatment

Vaccinations
VaccinatioID -PK
Vaccination

Surgeries
SurgeryID -PK
Surgery

When you do this you will need to develop forms and subforms so the data will
"mesh" e.g. you will have a Family info form and a Desireedcriteria subform
(continuous) linked on FamilyID. That way you can enter more than one
desired criteria for a pet for which a family is looking.
You would also have a Pet form with a MedicalTreatment subform linked by
PetID similar ot the above so you can enter all the treatments a pet gets.
Have and Adoption form with the pet info and a combo box based on the family
table so that when you select a family the FamilyID is the bound column who's
source is FamilyID in Pets.
Hope this helps,
Bob