0

I am not so into database and I have the following doubt:

I have this room_rate table that represent the rate reserverations of an accomodation system:

id                    bigint(20) unsigned   NO  PRI     NULL    auto_increment
rate_name             varchar(255)          NO          NULL    
rate                  double                NO          NULL    
date_from             datetime              NO  PRI     NULL    
date_to               datetime              NO  PRI     NULL    
id_room_tipology_fk   bigint(20) unsigned   NO  PRI     NULL    
time_stamp            datetime              YES         NULL

I have that the primary key is composed by the following field:

1) id: an auto increment id.

2) date_from: the starting date.

3) date_to: the edning date.

4) id_room_tipology_fk: the room tipology link to another table.

A record of this table simply represent how much is the cost of a room beloning to a tipology of rooms (represented by id_room_tipology_fk) in a specific range of time represented by the date_from and date_to dates.

I want to avoid that a same room tipology have 2 records in the table in the same range of time.

I am asking if using the previous primary key subset is good or if I have to exclude the id field.

Basically I can't have something like this:

1    rate1    50    01/01/2017    10/01/2017    5   04/12/2016
2    rate2    70    01/01/2017    10/01/2017    5   06/12/2016

Because I can't have 2 rate for the same room tipology (5) in the same period.

What is the best solution to correctly model this situation?

AndreaNobili
  • 40,955
  • 107
  • 324
  • 596
  • To handle ranges of time, you are going to have to use a trigger in MySQL. – Gordon Linoff Dec 04 '16 at 16:21
  • @GordonLinoff No, maybe I was not clear...The record represent how much is the cost of a room in a specific range of time. The room renter can't insert the same price for the same range of time...this is the constraint that I have to model – AndreaNobili Dec 04 '16 at 16:23
  • @GordonLinoff or maybe am I missing something? – AndreaNobili Dec 04 '16 at 16:28
  • . . Have you considered that different time ranges could overlap, but not be exactly the same? – Gordon Linoff Dec 04 '16 at 18:46
  • @GordonLinoff No, it can't happen in my requirment. I need to have only a record for a specific room typology in a specific range of time. How can I handle this situation? – AndreaNobili Dec 04 '16 at 19:19
  • What do you mean by "tipology"/"typology" (it's not a word)? Do you mean, topology? Ie a collection/arrangement of rooms booked at once? Or "type", ie a room collection/kind? – philipxy Dec 10 '16 at 10:11
  • You are not clear: Do you need to declare a constraint that no two rows with the same id_room_tipology_fk have date_from-date_to intervals that overlap or touch, or are we allowed to just assume that they won't? *If* that condition is guaranteed then one rate per id_room_tipology_fk-interval pair is already guaranteed. (See my answer.) But if we can't assume then enforcing "the room renter can't insert the same price for the same range of time" requires dealing with intervals, which is more complex. – philipxy Dec 10 '16 at 10:24
  • Also you are not clear about "period" or "specific range of time". Do you mean there can't be two rates for the same id_room_tipology_fk at any time in the application, or there can't be two rates for the (same date_from-date_to) interval-id_room_tipology_fk pair in the table? Those are only the same thing if intervals for a id_room_tipology_fk can't overlap in the table. – philipxy Dec 10 '16 at 10:32

2 Answers2

0

Based on the explanation in the comments, you can just use a unique index/constraint. A simple way is to build a unique index:

create unique index unq_roomrate_room_datefrom
    on room_rate(id_room_tipology_fk, date_from);

This only uses the start date, but you can include the end date as well.

You can put the same check into the table definition, if you prefer:

constraint unq unq_roomrate_room_datefrom unique (id_room_tipology_fk, date_from);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Assuming that the table doesn't ever have the same id_room_tipology_fk with overlapping date_from-date_to ranges:

Although (id, date_from, date_to, id_room_tipology_fk) might be unique not null, it's not a primary key. Because (id), (date_from, id_room_tipology_fk) and (date_to, id_room_tipology_fk) all uniquely identify a row while no subset of any of them does, so they would all be candidate keys (declared unique not null), one of which you could choose to declare as primary key instead.

Each interval-id_room_tipology_fk pair is unique, so it can only appear with the one rate that is in the one row it appears in. So you don't need to add a constraint.

But if there's no guarantee for non-overlap then you need to enforce it with a constraint you have declare in a trigger. See the question & answers at Find date range overlaps within the same table, for specific user MySQL.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83