Access Database design question

J

jcfagan

Access 2007 - Microsoft Vista operating system

I am doing computer consulting for a friend that has a dog grooming
business. This person has a brand new Acer desktop computer and an Acer
laptop. Both computers are set-up on a wireless-g network sharing an internet
connection thu a local cable modem.

I want to develop an access 2007 application for his dog grooming business.
The Acer laptop would run the access 2007 execution program and the Microosft
Office Access 2007 development software would reside on the Acer desktop.
Backup would consist of copying the database file from laptop to the desktop.

Here is my preliminary table design:

Dog Grooming Transaction Table
transaction number (auto number field) primary key
transactiom month
transaction day
transaction year
pet name/owner last name (ex. Fido - Smith Rover - Smith)
service code (select code from service table)
amount (dollar amont of service rendered)
next appointment month
next appointment day
next appointment year
next appointment time

Customer Table
customer number (primary key)
customer last name (pet owner last name)
customer first name (pet owner first name)
customer middle initial (pet owner middle initial)
customer address
customer city
customer state
customer zip
customer work phone
customer cell phone
customer email address

Service Code Table
service code number (primary key)
service desciption
service price

Pet Table
pet name/customer last name (ex. Fido - Smith for duplicates fido2 - Smith)
(the ***(The above field would be the primary key)
customer number (pick from customer table)
breed of animal
age
male/female
neutered (yes/no)
spayed (yes/no)
vet name
vet phone
rabies shot due
dhlpp due
other innoculations
health problems
deaf
blind
allergies
injuries
other problems
inside dog (yes/no)
house trained (yes/no)
repond to strangers (yes/no)
dog biiten anyone (yes/no)
circunstances
dog obey any commands (yes/no)
what commands
dog groomer's name before this service
where
how did you learn of our service (pick from list)
special instructions for grooming this animal
digital picture of the dog

I think my design allows for multiple pets per customer by using the pet
name and owner last name as the primary key field in the pet table.

Question:
Is this the most efficient database design for a 1 man dog grooming business?
Comments and suggestions?

Thank you in advance for your help!
 
B

Bob Quintal

Access 2007 - Microsoft Vista operating system

I am doing computer consulting for a friend that has a dog
grooming business. This person has a brand new Acer desktop
computer and an Acer laptop. Both computers are set-up on a
wireless-g network sharing an internet connection thu a local
cable modem.

I want to develop an access 2007 application for his dog grooming
business. The Acer laptop would run the access 2007 execution
program and the Microosft Office Access 2007 development software
would reside on the Acer desktop. Backup would consist of copying
the database file from laptop to the desktop.

Here is my preliminary table design:

Dog Grooming Transaction Table
transaction number (auto number field) primary key
transactiom month
transaction day
transaction year
pet name/owner last name (ex. Fido - Smith Rover - Smith)
service code (select code from service table)
amount (dollar amont of service rendered)
next appointment month
next appointment day
next appointment year
next appointment time

Customer Table
customer number (primary key)
customer last name (pet owner last name)
customer first name (pet owner first name)
customer middle initial (pet owner middle initial)
customer address
customer city
customer state
customer zip
customer work phone
customer cell phone
customer email address

Service Code Table
service code number (primary key)
service desciption
service price

Pet Table
pet name/customer last name (ex. Fido - Smith for duplicates fido2
- Smith) (the ***(The above field would be the primary key)
customer number (pick from customer table)
breed of animal
age
male/female
neutered (yes/no)
spayed (yes/no)
vet name
vet phone
rabies shot due
dhlpp due
other innoculations
health problems
deaf
blind
allergies
injuries
other problems
inside dog (yes/no)
house trained (yes/no)
repond to strangers (yes/no)
dog biiten anyone (yes/no)
circunstances
dog obey any commands (yes/no)
what commands
dog groomer's name before this service
where
how did you learn of our service (pick from list)
special instructions for grooming this animal
digital picture of the dog

I think my design allows for multiple pets per customer by using
the pet name and owner last name as the primary key field in the
pet table.

Question:
Is this the most efficient database design for a 1 man dog
grooming business? Comments and suggestions?

Thank you in advance for your help!

A few general comments: date information should be stored in a
single date/time field not multiple fields, because you loos a rich
palette of date manipulation functions

pet name/customer last name (ex. Fido - Smith for duplicates fido2
- Smith)
You don't need this field, just pet name. You get the owner's name
by a lookup or query based on the ownernumber field. I'd use the
ownernumber plus the petname as a primary key. Access allows
defining a primary key as the combination of up to (iirc) 10 fields

Spayed and neutered are redundant, dependent on male/female.

You may want to put the health problems as subtables,
Health problems (problem code - PK, problem description)
and PET Health Problems (Owner number, petname, problem code as PK,
related to pets and problem codes.
 
R

rquintal

Access 2007 - Microsoft Vista operating system

I am doing computer consulting for a friend that has a dog grooming
business. This person has a brand new Acer desktop computer and an Acer
laptop. Both computers are set-up on a wireless-g network sharing an internet
connection thu a local cable modem.

I want to develop an access 2007 application for his dog grooming business.
The Acer laptop would run the access 2007 execution program and the Microosft
Office Access 2007 development software would reside on the Acer desktop.
Backup would consist of copying the database file from laptop to the desktop.

Here is my preliminary table design:

Dog Grooming Transaction Table
transaction number (auto number field) primary key
transactiom month
transaction day
transaction year
pet name/owner last name (ex. Fido - Smith Rover - Smith)
service code (select code from service table)
amount (dollar amont of service rendered)
next appointment month
next appointment day
next appointment year
next appointment time

Customer Table
customer number (primary key)
customer last name (pet owner last name)
customer first name (pet owner first name)
customer middle initial (pet owner middle initial)
customer address
customer city
customer state
customer zip
customer work phone
customer cell phone
customer email address

Service Code Table
service code number (primary key)
service desciption
service price

Pet Table
pet name/customer last name (ex. Fido - Smith for duplicates fido2 - Smith)
(the ***(The above field would be the primary key)
customer number (pick from customer table)
breed of animal
age
male/female
neutered (yes/no)
spayed (yes/no)
vet name
vet phone
rabies shot due
dhlpp due
other innoculations
health problems
deaf
blind
allergies
injuries
other problems
inside dog (yes/no)
house trained (yes/no)
repond to strangers (yes/no)
dog biiten anyone (yes/no)
circunstances
dog obey any commands (yes/no)
what commands
dog groomer's name before this service
where
how did you learn of our service (pick from list)
special instructions for grooming this animal
digital picture of the dog

I think my design allows for multiple pets per customer by using the pet
name and owner last name as the primary key field in the pet table.

Question:
Is this the most efficient database design for a 1 man dog grooming business?
Comments and suggestions?

Thank you in advance for your help!

A few general comments: date information should be stored in a single
date/time field not multiple fields, because you loos a rich palette
of date manipulation functions
pet name/customer last name (ex. Fido - Smith for duplicates fido2 - Smith)
You don't need this field, just pet name. You get the owner's name by
a lookup or query based on the ownernumber field. I'd use the
ownernumber plus the petname as a primary key. Access allows defining
a primary key as the combination of up to (iirc) 10 fields

Spayed and neutered are redundant, dependent on male/female.

You may want to put the health problems as subtables,
Health problems (problem code - PK, problem description)
and PET Health Problems (Owner number, petname, problem code as PK,
related to pets and problem codes.

Bob Quintal
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top