Database Design Question

Question

I have the following situation and would like to know your opinion and inputs. My applications has a company. Each company has a lot of departments, which in turn has a lot of users. I have a calendar at all the levels. So there is a central calendar for the company, and separate calendar for each departments, and separate calendar for each user. Idea is when the user is interested in an event that is in the company, he/she can add it to their calendar.Now,I need to have one or many tables for events.I was thinking whether

• I should have one table, which will have a field to uniquely identify each entity(company,department,user) and depending on who is querying, I can retrieve the results accordingly
• I should have multiple tables. One table for company, One table for departments, One table for users.

So it is more like having one table against three tables.

Thanks

Answer 1

Look at the application requirements - if the events for the different levels are essentially the same (have the same data requirements, behavior), you should probably just use the one table. If they are going to be different, use different tables.

Answer 2

I don't think events need to know if they belong to the company, department of user directly. I would suspect that events belong to a calendar and a calendar belong to the company, department or user?

So a Calendar table:

CALENDAR_ID

And then an event table

EVENT_ID

And an "EVENT_TO_CALENDAR" table (for the purposes of a many to many relationship:

CALENDAR_ID
EVENT_ID

If a user can see the event in the company calendar, they can say "add it to mine" that will create a new record in the EVENT_TO_CALENDAR table, with the same EVENT_ID but their unique CALENDAR_ID. The event is now linked to each calendar (the company's and the user's).

Answer 3

I think I would argue for three tables. First of all, if you choose one table, it will get three nullable foreign keys. This means you cannot guarantee consistency just from the database model but you have to guarantee it somewhere in your business logic. Second, as time passes you will very likely find out that a company calendar is slightly different than an employee or a department calendar. The latter may require an additional column, for example. You just cannot predict this.