0

I am trying to start working on an application, and while I have some code the time has come to work on my database model as well. In the next sections I will describe what my application should be doing, followed by what I expect of my database model, and in the end I am going to present to you my attempt thus far to solve my problem. I hope your experience will come in handy.

This is a simple application for creating appointments for services with specific providers. For example the application would enable you to create an appointment for haircut at 10:00 to be done by your favorite hairdresser John Doe.

Now here comes the catch. Namely each of the service providers, in this case our Mr. John Doe, could be at different location at different times and it may be incorrect to book an appointment with John Doe in one of our subsidiaries, if according to his work plan he is scheduled to be somewhere completely else.

These considerations have lead to me observe that I have following basic entities in my database:

  • Service ( represents the thing you can buy as a service, as said example would be a haircut )
  • Provider ( our John Doe )
  • Location ( location of the subsidiary )
  • Appointment ( the actual representation of an appointment for a service )

With these entities the following constraints represent for me "valid data" ( and do correct me if this should not be done in database ) :

  • A provider can only be at single location at specific time
  • A provider can only have a single appointment booked at specific time
  • A single appointment is completely provided by single provider ( you will see relevance of this constraint, when I present my solution which I have made up to now )
  • A single appointment is completely provided at single location
  • We can book an appointment with a specific provider at specific location only if the provider is scheduled to be at this location at this time.

I have managed to get most of the constraints implemented, but some always are seemingly not satisfiable with simple tools. I have decided that in order to simplify things, and avoid usage time intervals only specific time entries are allowed to be made in the database. Namely time intervals for bookings and locations are represented by collections of timestamps, which always have to be multiples of 5 minutes and must have 0 seconds. Here is my model up to now:

create table service (
    id serial primary key,
    name varchar(255) not null,
    unique(name)
);
create table provider (
    id serial primary key,
    name varchar(128) not null,
);
create table location (
    id serial primary key,
    name varchar(255) not null,
    unique(name)
);
-- This table specifies the exact times at which a provider is at a specific location
create table provider_location_time(
    id serial primary key,
    provider_id integer not null,
    location_id integer not null,
    time_block timestamp not null check (cast(extract(minute from time_block) as integer) % 5 = 0 and cast(extract(second from time_block) as integer) = 0),
    foreign key (provider_id) references provider(id),
    foreign key (location_id) references location(id),
    unique(provider_id, time_block) -- this constraint ensures that provider is only at single location at specific time
);

create table appointment(
    id serial primary key,
    service_id integer not null
);

-- Specifies the times which are reserved for single appointment
create table appointment_time(
    id serial primary key,
    appointment_id integer not null,
    provider_location_time_id integer not null, -- usage of this foreign key ensures that the appointment can only be made at locations and times where a provider is available as described in fifth constraint listed above
    foreign key (appointment_id) references appointment(id),
    foreign key (provider_location_time_id) references provider_location_time(id),
    unique(provider_location_time_id) -- ensures that single time slot available for provider at location is booked only for single appointment. Avoids double booking of providers by accident
);

Now given the model above I can not ensure the third constraint namely that:

Single appointment is completely provided by single provider

And due to inability to ensure the constraint above I can not ensure that an appointment is to be always completed at single specific location, since I can attach an appointment to arbitrary provider-location-time combinations.

Can this be solved by better design or are triggers my only option?

1

It's impractical to model all your application's rules using declarative referential integrity in your database. Think about your constraints in terms of either 1) data integrity, 2) business rules. Try rather hard to enforce the data integrity rules in your database, and expect other business rules to need to be checked in code.

Single appointment is completely provided by single provider

Why? Is this rule somehow fundamental to the business domain? What if you bake this rule into your data model and a customer wants to put multiple providers on a single appointment? Wouldn't a data model that allows this be more useful?

As to the details of your question, I don't see the appointment table being useful here. I would collapse it with the appointment_time table

create table appointment(
    id serial primary key,
    service_id integer not null
    provider_location_time_id integer not null, -- usage of this foreign key ensures that the appointment can only be made at locations and times where a provider is available as described in fifth constraint listed above
    foreign key (service_id) references service(id),
    foreign key (provider_location_time_id) references provider_location_time(id),
    unique(provider_location_time_id) -- ensures that single time slot available for provider at location is booked only for single appointment. Avoids double booking of providers by accident
);

That way one appointment has only one provider_location_time_id, and so only one provider.

1
  • Thank you for a bit of schooling on database design. I highly appreciate it as I am quite new in this. I have actually managed to enforce all of my required constraints, and have asked myself the same question with respect to single-provider-per-appointment constraint. I will post my model in separate answer, but I would accept yours as correct. Jul 12 at 7:30
0

Here is the solution I came up with, which respects all of my constraints and is written for Postgres:

create table provider (
    id serial primary key,
    name varchar(128) not null
);
create table service (
    id serial primary key,
    name varchar(255) not null,
    unique(name)
);
create table location (
    id serial primary key,
    name varchar(255) not null,
    unique(name)
);

create table appointment(
    id serial primary key,
    service_id integer not null
);

create table provider_location_time(
    id serial primary key,
    provider_id integer not null,
    location_id integer not null,
    time_block timestamp not null check (cast(extract(minute from time_block) as integer) % 5 = 0 and cast(extract(second from time_block) as integer) = 0),
    appointment_id integer,
    foreign key (provider_id) references provider(id),
    foreign key (location_id) references location(id),
    foreign key (appointment_id) references appointment(id),
    constraint PROVIDER_AT_SINGLE_LOCATION_AT_A_TIME unique(provider_id, time_block), -- Provider can be at single location at given time
    constraint APPOINTMENT_BOOKS_TIME_ONCE unique(appointment_id, time_block), -- appointment books a single time block once
    constraint APPOINTMENT_PROVIDED_BY_SINGLE_PROVIDER EXCLUDE USING gist (provider_id WITH <>, appointment_id WITH =),
    constraint APPOINTMENT_PROVIDED_AT_SINGLE_LOCATION using gist (appointment_id with =, location_id with <>)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.