access database designs

[jcfagan]

I want to create a database for a pet grooming business.
The preliminary design is 3 tables.
Table 1 is the transaction table consisting of transaction number, date &
time, customer number, service performed and pet name, dollar amount for
service.
Table 2 is the customer file consisiting of customer number, name, address,
phone number, email address.
Table 3 is the pet name file consisting of pet name/customer number, and all
pertinent information about the pet.

This design would allow multiple pets per customer.

 

[Jeff Boyce]

I didn't see a question in this...

If you use [pet name] in your [Transaction] table, how will you be able to
tell the difference between "Fluffy" and "Fluffy"?! Use a PetID as a
primary key in the [Pet] table, and use it as a foreign key in your
[Transaction] table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

You might also need to allow for multiple customers per pet, where a wife
brings in a pet one time and the husband/boyfriend/uncle/aunt/pet sitter the
next time

-Dorian

 

[jcfagan]

I was planning on using an auto number numeric field in the transaction table
for the primary key. Then I would use the combination pet name/owner name
for the pet table as the primary key. The pet owner (customer file ) would
consist of a customer number as the primary key. Each pet would have its
detailed information in the pet table (ex medical info, breed, etc...). This
would allow multiple pets per owner.

I didn't see a question in this...

If you use [pet name] in your [Transaction] table, how will you be able to
tell the difference between "Fluffy" and "Fluffy"?! Use a PetID as a
primary key in the [Pet] table, and use it as a foreign key in your
[Transaction] table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to create a database for a pet grooming business.
The preliminary design is 3 tables.
Table 1 is the transaction table consisting of transaction number, date &
time, customer number, service performed and pet name, dollar amount for
service.
Table 2 is the customer file consisiting of customer number, name,
address,
phone number, email address.
Table 3 is the pet name file consisting of pet name/customer number, and
all
pertinent information about the pet.

This design would allow multiple pets per customer.

 

[jcfagan]

The customer would be the pet owner only. I would set up the pet table with a
primary key of pet name and pet owner(customer). The transaction table would
have an auto number field as the primary key. The customer (owner) table
would have an customer number as the primary key. The pet table would contain
the bulk of the information about the pet (ex. breed , medical info, etc.). I
think this set up would allow multiple pets per owner(customer).

 

[John Nurick]

Won't there be lots of cases where the pet's owner is a child but the
bills are paid by a parent, maybe a non-resident parent with a surname
different from the child's?

 

[LauriS]

Then I would use the combination pet name/owner name for the pet table as the primary key.

That won't work. By using NAMES you limit yourself to only one occurance of
a person named Smith owning a pet named Sparky.

Give the Owners AND the pets unique identifiers - auto numbers will work
fine for that.

Owners
Owner ID (key - autonumber)
Owner First Name
Owner Last Name
Owner Address
etc.

Pets
Pet ID (auto-number)
Owner ID (the number from the Owner table)
Name
Species
Breed
etc.

Most grooming shops (and vets) ask for the last name of the PET (which would
be the owners last name) and then ask for the pet's name. It really won't
matter who is the true 'owner' of the animal.

Hope this helps.

Lauri S.