I tried to find a related question similar to mine, but couldn’t. I apologize if this is a repeat.
I have an odd situation and just need advice. I have a company calendar that shows events as well as employee time off (PTO). These are in an event table.
id user_id text start_date end_date
id user_id text reason length start_date end_date type status overall_status
Here’s where it gets a bit messy. There are a few identical columns in both tables, such as
end_date. If a user submits a request to change the PTO, I would need the make the changes in two tables.
My question is, is it better to combine the tables or update the related Event when a change is made? OR, is it better to find a way to pull data from two tables and combine them into one variable for display on the calendar.
Hope that’s not confusing.
My advice is allowing PTO to be a sub-type of event.
If Event is a super-type it will have an additional field: type.
If type=PTO, then there should be an item in the PTO table, with the event_id to link them.
You can read about super-type/sub-type relationships on your favorite database information website, but a sub-type minimizes data duplication, and keeps similar data in one place. So, all events have a start and end date, but not all have a reason like vacations do.
Once the tables have been changed, you could add the start and end date from each PTO row to Event, and add the reason and other PTO data to the new, smaller PTO table. Then you could drop the PTO table once all the data has been moved.