0

I have a mySQL schema containing in which there are two tables: stays and service_records. 

Columns for service_records: `stayId`, `serviceCode`, `staffId`, `serviceDate`, `serviceTime`

Columns for stays: `stayId`, `hotelId`, ...`, `checkinDate`, `checkinTime`, `checkoutDate`, `checkoutTime`, `billingId`

Here are the create table script for each table:

*************************** 1. row ***************************
       Table: stays
Create Table: CREATE TABLE `stays` (
  `stayId` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `hotelId` int(9) unsigned NOT NULL,
  `roomNumber` varchar(5) NOT NULL,
  `customerId` int(9) unsigned NOT NULL,
  `numOfGuests` tinyint(4) NOT NULL,
  `checkinDate` date DEFAULT NULL,
  `checkinTime` time DEFAULT NULL,
  `checkoutDate` date DEFAULT NULL,
  `checkoutTime` time DEFAULT NULL,
  `billingId` int(9) unsigned NOT NULL,
  PRIMARY KEY (`stayId`),
  UNIQUE KEY `billingId` (`billingId`),
  KEY `hotelId` (`hotelId`,`roomNumber`),
  KEY `customerId` (`customerId`),
  CONSTRAINT `stays_ibfk_1` FOREIGN KEY (`hotelId`, `roomNumber`)     REFERENCES `rooms` (`hotelId`, `roomNumber`),
  CONSTRAINT `stays_ibfk_2` FOREIGN KEY (`customerId`) REFERENCES     `customers` (`customerId`),
  CONSTRAINT `stays_ibfk_3` FOREIGN KEY (`billingId`) REFERENCES     `billing_info` (`billingId`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=latin1

and 

| service_records | CREATE TABLE `service_records` (
  `stayId` int(9) unsigned NOT NULL,
  `serviceCode` char(4) NOT NULL,
  `staffId` int(9) unsigned NOT NULL,
  `serviceDate` date NOT NULL,
  `serviceTime` time NOT NULL,
  PRIMARY KEY (`stayId`,`serviceDate`,`serviceTime`),
  KEY `serviceCode` (`serviceCode`),
  KEY `staffId` (`staffId`),
  CONSTRAINT `service_records_ibfk_1` FOREIGN KEY (`stayId`) REFERENCES `stays` (`stayId`),
  CONSTRAINT `service_records_ibfk_2` FOREIGN KEY (`serviceCode`) REFERENCES `services` (`serviceCode`),
  CONSTRAINT `service_records_ibfk_3` FOREIGN KEY (`staffId`) REFERENCES     `service_staff` (`staffId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Question1: I want to make sure the services times are inside the stay time window. I was wondering how I could use MySQL constrains to enforce service times to be in the stay range!

Question2: If I have already records in my tables, it that possible to update the tables (Without deleting the tables ) with proper column constraints? Of course, I would edit the effected records.

Your help would be greatly appreciated.

CS

chikitin
  • 762
  • 6
  • 28
  • 1
    Why store the date/times as two columns instead of using a timestamp type and just one column for each value? That would make it easier to do range comparisons and allow indexes to be used, speeding queries up quite a bit. – Sloan Thrasher Apr 09 '18 at 23:59
  • 1
    You won;t be able to use a constraint, however a trigger could test on insert/update to ensure the value is within the range and either allow the insert/update or fail it. – Sloan Thrasher Apr 10 '18 at 00:00
  • Thank you very much Sloan. Bammer say, I can use check constraints! So triggers or check constrains? It seems MySQL does not reinforce check constrains. I guess this is what I need to use: https://stackoverflow.com/questions/9734920/can-a-mysql-trigger-simulate-a-check-constraint – chikitin Apr 10 '18 at 01:01

0 Answers0