Re: ER Diagram Help

maccessgirl_at_gmail.com wrote:
> 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. Would someone
> please explain to me the relationships the see in this situation
>
> Thanks in advance,
> MACCESSGIRL
>

I would take Adoption Date out of Pets and Date of Adoption out of Families and make another entity - Adoptions - that related the two.

I don't think Pets alone is sufficient. I suspect you need something for Pets taken in (that would have an Intake Date) and Pets adopted out. In my world, those would be different tables. Too, not all animals taken in will be adopted; where do they go? More tables!

The Desired Criteria doesn't identify who has the want. And you probably want to affix some sort of date and invent a rule that prevents them from lingering forever.

Treatments? Your design suggests lots of other tables (all those IDs) that I don't think you need. A categorizing code, description, date, perhaps the provider, and (of course) something identifying the animal are probably all you'll need. Received on Sat Jul 29 2006 - 07:18:12 CEST