should i consolidate these database tables . .

Question

i have an event calendar application with a sql database behind it and right now i have 3 tables to represent the events:

Table 1: Holiday
Columns: ID, Date, Name, Location, CalendarID

Table 2: Vacation
Columns: Id, Date, Name, PersonId, WorkflowStatus

Table 3: Event
Columns: Id, Date, Name, CalendarID

So i have "generic events" which go into the event tableand special events like holidays and vacation that go into these separate tables. I am debating consolidating these into a single table and just having columns like location and personid blank for the generic events.

Table 1: Event:
Columns : Id, Date, Name, Location, PersonId, WorkflowStatus

does anyone see any strong positives or negative to each option. Obviously there will be records that have columns that dont necessarily apply but it there is overlap with these three tables.

Answer 1

If it were my decision, i'd condense them into one table. I'd add a column called "EventType" and update that as you import the data into the new table to specify the type of event.

That way, you only need to index one table instead of three (if you feel indexes are required), the data is all in one table, and the queries to get the data out would be a little more concise because you wouldn't need to union all three tables together to see what one person has done. I don't see any downside to having it all in one table (although there will probably be one that someone will bring up that i haven't thought of).

Answer 2

Either way you construct it, the application will have to cope with variant types. In such a situation I recommend that you use a single representation in the DBM because the alternative is to require a multiplicity of queries.

So it becomes a question of where you stick the complexity and even in a huge organization, it's really hard to generate enough events to worry about DBMS optimization. Application code is more flexible than hardwired schemata. This is a matter of preference.

Answer 3

Keep them in 3 separate tables and do a UNION ALL in a view if you need to merge the data into one resultset for consumption. How you store the data on disk need not be identical to how you need to consume the data so long as the performance is adequate.

As you have it now there are no columns that do not apply for any of the presented entities. If you were to merge the 3 tables into one you'd have to add a field at the very least to know which columns to expect to be populated and reduce your performance. Now when you query for a holiday alone you go to a subset of the data that you would have to sift through / index to get at the same data in a merged storage table.

If you did not already have these tables defined you could consider creating one table with the following signature...

create table EventBase (
  Id int PRIMARY KEY,
  Date date,
  Name varchar(50)
)

...and, say, the holiday table with the following signature.

create table holiday (
  Id int PRIMARY KEY,
  EventId int,
  Location varchar(50),
  CalendarId int
)

...and join the two when you needed to do so. Choosing between this and the 3 separate tables you already have depends on how you plan on using the tables and volume but I would definitely not throw all into a single table as is and make things less clear to someone looking at the table definition with no other initiation.

Answer 4

How about sub-typing special events to an Event supertype? This way it is easy to later add any new special events.

Answer 5

Or combine the common fields and separate out the unique ones:

Table 1: EventCommon

Columns: EventCommonID, Date, Name

Table 2: EventOrHoliday

Columns: EventCommonID, CalendarID, isHoliday

Table3: Vacation

Columns: EventCommonID, PersonId, WorkflowStatus

with 1->many relationships between EventCommon and the other 2.

Answer 6

Data integrity is the biggest downside of putting them in one table. Since these all appear to be fields that would be required, you lose the ability to require them all by default and would have to write a trigger to make sure that data integrity was maintained properly (Yes, this must be maintained in the database and not, as some people believe, by the application. Unless of course you want to have data integrity problems.)

Another issue is that these are the events you need now and there may be more and more specialized events in the future and possibly breaking code for one type of event because you added another specialized field that only applies to something else is a big risk. When you make a change to add some required vacation information, will you be sure to check that it doesn't break the application concerning holidays? Or worse not error out but show information you didn't want? Are you going to look at the actual screen everytime? Unit testing just of code may not pick up this type of thing especially if someone was foolish enough to use select * or fail to specify columns in an insert. And frankly not every organization actually has a really thorough automated test process in place (it could be less risk if you do).

I personally would tend to go with Damir Sudarevic's solution. An event table for all the common fields (making it easy to at least get a list of all events) and specialized tables for the fields not held in common, making is simpler to write code that affects only one event and allowing the database to maintain its integrity.