0

I have a database where I need to have a Meeting Table where that table has a date of type Date. I need to prevent users to insert dates that are holidays our weekends into that table. What is the best approach for this?

This is my schema:

User
user_id
cellphone
name
role_id

Role
role_id
role_type (Veterinário,Assistent,Client)

Meeting
meeting_id
date (day+ hour) Unique Key //need holiday and weekends prevention here
user_id_Vet
user_id_Client
José Nobre
  • 4,407
  • 6
  • 20
  • 40
  • Calendar table. – Lukasz Szozda Aug 20 '18 at 16:28
  • Yes Calendar Table. Weekends are easy. Most US holidays are fairly simple and will follow a pattern year over year. Easter will drive you bonkers. Your regional and or business holidays may vary. >> https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server >> That's MS SQL Server, and I've always been partial to Aaron Bertrand's calendar table scripts, but you should be able to modify that code to work with MySQL. – Shawn Aug 20 '18 at 18:29

2 Answers2

2

Simply make a table of holiday in your database and before inserting record to your meeting table, check whether the selected date is in your holiday table or not. If selected date is in your holiday table then return message using JavaScript "This is a holiday". Else return success.

Abhishek Diwakar
  • 466
  • 1
  • 6
  • 18
  • Ok, and what about weekends? – José Nobre Aug 20 '18 at 16:33
  • Using [DatePart](https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) you can identify weekday. – JosephC Aug 20 '18 at 16:37
  • What language you are using with SQL? Are you just executing SQL Queries or you are using SQL with languages like PHP or other? – Abhishek Diwakar Aug 20 '18 at 16:40
  • I am using Java(Spring framework) to do an API for mobile consumption – José Nobre Aug 20 '18 at 16:43
  • Visit this link https://stackoverflow.com/q/5270272/9196959. Here you can see a method to get day from date. Just get date from user as string and then use the method given on this link to get day from date. Now if output is "sat" or "sun". Return error else return success. Hope it help – Abhishek Diwakar Aug 20 '18 at 16:56
0

Weekends are the easy bit:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(dt DATE NOT NULL);

INSERT INTO my_table SELECT '2018-08-19' FROM (SELECT 1) n WHERE WEEKDAY('2018-08-19') <5;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT INTO my_table SELECT '2018-08-20' FROM (SELECT 1) n WHERE WEEKDAY('2018-08-20') <5;
Query OK, 1 row affected (0.50 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM my_table;
+------------+
| dt         |
+------------+
| 2018-08-20 |
+------------+
1 row in set (0.00 sec)
Strawberry
  • 33,750
  • 13
  • 40
  • 57