0

I am in the process of designing a part of a Database for a pet project of mine, and I am in this predicament.

I have 3 tables: PET, BOOKING, SERVICE.

PET (attributes):-
    Pet_Id => Primary Key,
    Pet_Name,
    Pet_Species,
    Pet_Breed,
    DoB,
    Gender

BOOKING (attributes):-
    Booking_Id => Primary Key,
    Booking_Location,
    Booking_Date

SERVICE (attributes):-
    Service_Id => Primary Key,
    Service_Name

The Situation is that, M Bookings (BOOKING) service N Pets (PET) i.e there is an m:n cardinality relationship between Bookings and Pets. The table formed due to this relationship is called SCHEDULE with the primary keys of the two tables combining to form the primary key of SCHEDULE

I established this relationship due to the fact that there are multiple pets being serviced through a particular booking at a particular time and that a particular pet can be serviced through many bookings at different times.

Now the single pet in a particular booking schedule (SCHEDULE) can have multiple services provided for them which is akin to an m:n relationship between Schedules (SCHEDULE) and Services (SERVICE).

How do I handle this case? The first thing that came into mind was to have an m:n relationship between the schedule table and the services table as shown in the picture below. enter image description here

But then Schedule is already an m:n table. Can a table say 'A' created through an m:n relationship be in a relationship of any cardinality with a table 'B'.

If possible, how do I construct it? If not, how do I design the relationships between the above-mentioned tables?

Pavan Vasan
  • 391
  • 1
  • 9
  • 28
  • Please text, not images/links, for text, including tables (use code format) & ERDs (use DDL). Re " Can a table say 'A' created through an m:n relationship be in a relationship of any cardinality with a table 'B'.": What does your textbook say & where are you stuck? Please don't ask us to rewrite it. – philipxy Feb 23 '19 at 03:04
  • @philipxy, it is not in any textbook, this is one of the scenarios I have in my project and I am asking for help – Pavan Vasan Feb 23 '19 at 05:37
  • I am not talking about following that example in your information modeling & database design texbook/reference, I am talking about following the definitions, theorems, algorithms & heuristics as presented in your textbook/reference as you apply them to this example. All you are doing is asking for yet another presentation to be written. (Moreover there are many different design method variants.) That's inadequate research effort, not useful & too broad (and neverthless a duplicate). Not all requests for help are on-topic questions. See the voting arrow mouseover texts & [ask]. – philipxy Feb 23 '19 at 06:22
  • Possible duplicate of [When to use Associative entities?](https://stackoverflow.com/q/38366466/3404097) – philipxy Feb 23 '19 at 06:22

0 Answers0