Advices to construct Conceptual Model of Data

I need help to build the good conceptual model of data for my project with relations, pivot tables …

This is my problem :

In a form, a company can make a celebration booking, but there are several constraints : according to the day of date, a celebrant is affected to celebrate and that same celebrant can celebrate X celebrations for the half day. If half day is morning, for example, a select with hours is showed like (9:00, 9:30, 10:00, 10:30, …, 12:00) and in this select, if celebrations exist, I would remove the hours already taken.

So what is the best way to convert this to tables and relations ?

Right now, I had thought of this

--companies--
id
name

--celebrants--
id
name
number_max_celebrations

--affectations--
id
celebrant_id
 half_day_working (morning or afternoon)

--bookings--
id
… 
desired_hour

--affectation_booking --
affectation_id 
booking_id

Thank you for helping me build a good database so that development is as simple as possible.

TerrePorter

6 years ago

Level 12

Ok, not sure I totally understand what your asking.

Is this right?

Each Company -> has many celebrants -> each celebrant has one affection per date restricted to (morning or afternoon) -> that affection can have many bookings (aka times) per the time frame (morning or afternoon) up to the limit specified in the affection record

Please or to participate in this conversation.