Table Relationship Problem - Help determine Relationships 3

[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

 

[Golom]

In the Pets and Family tables you probably want PetID and FamilyID fields because two fields named ID tend to be confusing when they refer to different things.

The Medical Information table probably needs to be
[tt]
tblMedicalInformation
PetID
TreatmentType (V, S, T)
TreatmentDescription
TreatmentDate
... Other Info About the Treatment ...
[/tt]

You also need some way to determine when a vaccination or other medical followup is due.
[tt]
tblTreatments
TreatmentID
TreatmentType
FrequencyPeriod (e.g. Years, Months, Days, etc.)
FrequencyCount (e.g. Number of Years between treatments)
[/tt]


There should be a table called Adoptions of the form
[tt]
tblAdoptions
PetID (PK)
FamilyID (PK)
DateAdopted
[/tt]
and the fields Date of Adoption and 1 or more pets should be removed from the Family table and AdoptionDate should be removed from the Pets table.

 

[dhookom]

Great answer and a star to Golom.

The same question was asked in the MS Public Access news groups and I expect MSAccessGirl will get the same advice.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MACCESSGIRL]

Golom,

Thank you soo very much for the insight. I was wondering could you answer a few more questions.

How do I address the Thank you letter part of this? Speciifcally, do I treat it as an entity

Thank you letter
Yes
No

Also, is the desired criteria relevant as an entity. Further, how can I make a relationship of wanting to know how many pets have been adopted by the same family. Meaning the same pet can be adopted and brought back by one family.

Thanks again,
ACCESSGIRL

 

[lespaul]

Maybe add another field to tblAdoptions called 'Returned' (yes/no) can handle the identification of what family returned what animal.

As far as the 'Thank you' letter, that's not an entity...that's a procedure that needs to be performed on a selection of adoptions. Maybe again, add a field to tblAdoptions, PrintThankYou (yes/no) and PrintDate and when the adoption takes place you can indicate if a TU letter needs to be generated, and once you print the letter update PrintDate so you don't duplicate letters.

You can make the 'Print Thank you letters' a function/menu choice and when the user selects that function you find all the adoptions where PrintThankYou = 'Yes' and PrintDate is null.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MACCESSGIRL]

Lespaul, Thank you!!! I didn't think a Thank you letter would be an entity. Your explanation is perfect.

Another question, should I have that desired criteria entity. I need to create something where we can look and see if we got a pet in that meets a family's criteria

Thanks again

 

[lespaul]

another table:

tblRequest
FamilyID
AnimalType
AnimalBreed
RequestFulfilled

then when you add a pet to the 'available' pets, run a query to see if anyone has requested that breed/type.

Leslie

 

[Golom]

Just to extend Leslie's suggestion. You need a bit of analysis about what to include in the tblRequest structure. Some candidates that come to mind are

[li]Species (Dog, Cat, Walrus, etc.)[/li]
[li]Breed[/li]
[li]Age (or age range)[/li]
[li]Color[/li]
[li]Size (S,M,L)[/li]

You would then need to incorporate those characteristics in tblPets to permit searches for a match.

Other things like "house broken", "gentle personality", etc. may be included in some free-form text field but they are usually not searchable and generally would not be known for each pet received in the facility.