Mysql – Need advice on combining tables or leaving as separate tables

database-designMySQLPHP

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.

Events Table

id
user_id
text
start_date
end_date

PTO Table

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 user_id, start_date, and 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.

Best Answer

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.

Related Question