I'm currently trying to create a pet store database in access for a class assignment.

Scenario: The pet store provides three services an adoption service, medical service and a pet treatment service.
The adoption service is where customers can come in and choose from a list of pets then pay an adoption fee and take the pet home.
The medical service is where customers can bring their pets in for surgery, vaccinations, neutering and things of that nature.
The pet treatment service is where customers either choose from a grooming service or a wash and cut service and things in that nature.

I have my tables but I'm not sure how the relationships are suppose to work. I was wondering if someone out there could give a little insight to my little dilemma.

Tables I've got so far are as follows.

AdoptionService
AvailablePets
Booking
Customer
CustomerPet
MedicalService
PetService
Vet
PetTreatment
Service

I may have tables that are missing and tables that shouldn't be in here.
Any help would greatly be appreciated.


I also posted an image below on how I created the relationships.

Welcome to UtterAccess.

Your relationships are not appropriate, but it IS important that you've taken a stab at doing the work before coming here to ask for help. So here is some feedback.

So, each table should represent ONE thing, and the relevant attributes of that thing. Look at the CustomerPet table, for example. "ServiceID" does NOT tell you anything about a particular pet. A Pet, on the other hand, DOES come into the shop for some sort of Service during a Booking.

It is also the case that a Booking is for a particular service, not the other way round, as your diagram shows. In fact, in nearly ever table, you have the relationship backwards. A vet does not RECEIVE Medical Services; a vet DELIVERS them during a treatment. PetTreatment appears to be the only one that's close to correct. A pet treatment would involve the pet, the vet and the medical service delivered by the vet in that treatment.

Give it another go and show us the next version. We're always happy to assist, when it's clear the person is actually working at the problem themselves and not just trying to get us to do their homework for them.

Here are some good readings on what you are trying to accomplish.

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• MSKB 283878: Description of the database normalization basics
  
• Presentation and sample db
• Crystal's ACCESS Basics for Programming
  
• Roger Carlson’s Blog on Normalization

Thanks for the warm welcoming most importantly the quick reply.
You're a scholar and a gent George appreciate the feedback.

What I did was I decided to give this relationships thing another go before I read up on those links you've supplied.
Infact I might start reading up on those links whilst I wait for some more replies.

I'm not asking anyone to do my homework, just some feedback on what is wrong and what I can correct, is what I'm after.

So based on George's first reply I came up with another version.

Below is another noob attempt at this relationship thingamajig. Any type of feedback is welcome!

Read. It's a great way to learn.

Much better but still not there yet. The problem you seem to be having is thinking each type of service provided is a separate entity. Its not the type of service is an attribute of the Entity service.

So your Booking table is OK, except it should link to PetID, not CustomerID since a customer can have multiple pets. And I'm assuming your Service Table is to be used for billing purposes. BookingID doesn't belong in there. But you should have a ServiceTypeID that links to the type of service performed and a Service Type table that lists all the services.

Keep going and well continue to help.

I had to chuckle at this assignment, and to (a bit wryly) wish you luck. It's a thumbnail description of PawTrax, the database used by the Los Angeles SPCA for all their animal shelter data. I've been working with them on it for ten years now and it's not done...

You're getting good advice from George and Scott, I'll sit back and chime in if there's anything I can contribute.

By all means chuckle away Mr Vinson, feel free to laugh at my pain.

God I hate databases!

Thanks again everyone for the help.

Yes George I have started reading those articles, I'm just not sure if I can retain all that information.

Although I have created another version let's just hope 3rd times the charm. I emphases the word HOPE!

I got rid of a couple of entities(tables) and stored those as attributes in the service table as requested by ScottGem and I also linked the customerpets table to the booking table also known as a junction table, between the customerpets and service table lol, I think I read that in one of those articles. I may have learned something today.

My apologies for the tone of my message! I'm not laughing in ridicule, I'm (wryly!) laughing in sympathy at the burden of this assignment.

Your current relationships seem to be well on the way. I would suggest considering a table of Treatments rather than just a freeform text field for treatment; this would let you store the current cost of (say) flea baths, earmite removal, spay or neuter, etc. so you would both have a controlled vocabulary of treatments that could just be picked from a combo box, and automate the entry of the treatment cost.

Best of luck with this, and we'll all help as best we can!