Database architecture - split vs. union vs. ?

Question

Hey there,

Got an interesting scenario I'd like to run past you guys re: DB architecture.

There will be two entities:

• Application: This is more or less the core of the app we are building. It has a start and end date along with about 15 other properties.
• CalendarEvent: This a less used entity. It has a start and end date with maybe 3 other properties.

The app we're building is mostly concerned with the applications. There will be a calendar page where both applications and events are listed however. Given the similarities between the entities (start and end dates) and the fact that there will be a page where they will both be listed, how would you cut this?

Ideas:

• Have an event table w/ start date, end date & type (application or calendarevent). Have an application table which references the event table. Have a calendarevent table which references the event table?
• Keep the tables seperate and use union in queries where both entities are required

Thanks in advance, Alex

EDIT: Leaning to towards the idea of having a "Period" table w/ start date and end date. Then both the "Application" and "CalendarEvent" tables can have a foreign key reference to a record in the period table.

Answer 1

The first option seems more scalable, as you can eventually add more types to the events.

The only drawback on this is the joining/loss of foreign key, but if this is not an issue, go with this design. But using the correct indexes should allow for good performance.

Me thinks, that using a union, you would have to specify the type in any case to distinguish between the 2 tables.

Answer 2

What are you trying to model with the Application entity? In the real world, I don't view an Application as a special type of Event. However, maybe you could model a Schedule, with a one-to-one relationship with Application, especially since the Schedule will probably have some of its own attributes you'll want to track (when it slipped, etc). Then you could grab all of the Schedules and throw them on the calendar, and maybe somehow mix the Schedule with CalendarEvent.

...or you could keep them just the way they are. Just because some objects/entities have fields/attributes in common, you're not required to abstract some other concept from them. Tons of tables have some kind of 'description' column... that doesn't mean you have to have a ThingThatCanBeDescribed (Descriptable?) table.