Database design for meetings on a calendar

[stubarny]

Hello,

 

I am trying to code a meeting functionality whereby users can create meetings, invite people to meetings and respond (accept/decline) meeting invites.

 

I am coding a page view that shows a meeting invitation along with a list of of users' (latest) responses (Accept/Decline).

 

To do this I was simply going to join the "meetings" table (which has a list of meetings) with the "responses" table (which has a list of users' responses). However it's occured to me that users may respond several times to an invite (e.g. accept at first and then decline later on), which would cause several entries in the "responses table" and so causing me to need to find the most recent response for each user for a given meeting).

 

I know this can be done with a complicated SQL query but it strikes me as an inelegant solution, slow and maybe not scaleable? Please could someone suggest a better approach?

 

Thanks for your help,

 

Stu

[ignace]

However it's occured to me that users may respond several times to an invite (e.g. accept at first and then decline later on)

 

They would still only change one record, not creating several.

 

participant (ptc_id, ptc_email)
meeting_place (plc_id)
meeting (mtg_id, mtg_uniqid, plc_id, mtg_reqby, mtg_subject, mtg_body, mtg_created_on, mtg_from_dt, mtg_till_dt, mtg_is_resched, mtg_resched_original)
meeting_request (mtg_id, ptc_id, mr_response)

 

A simple table structure, where one, can request a meeting with several other participants (incl. himself). The mr_response is either ACCEPTED, DECLINED or RESCHEDULE.

 

When one of the participants wants to reschedule it creates a new meeting and attaches all participants to this new meeting and refers back to the original meeting request. I should note that a rescheduled meeting can point to a previous rescheduled meeting.

 

The mtg_uniqid should be the same for all of the same meetings. So in this model you would need to find the last known rescheduled meeting which can be done by desc sorting on mtg_created_on where mtg_uniqid is the same.

 

This is a very basic model off-the-bat and may need some serious tweaking.

[stubarny]

"

However it's occured to me that users may respond several times to an invite (e.g. accept at first and then decline later on)

"

 

 

They would still only change one record, not creating several.

 

Hi ignace,

 

I don't like UPDATE queries because they effectively DELETE and INSERT. The deleted data is lost without trace, along with any audit trail of "which users did what and when" (along with ip addresses and timestamps). Is there another way of doing this that maintains a record of all user actions? :-)

[ignace]

Is there another way of doing this that maintains a record of all user actions?

 

Add a timestamp to the meeting_request? When you left join then on this table you will have to desc sort on this timestamp and then group by, I think.

 

PS I forgot to include that mr_response can also have the value PENDING... except for the requester which will have REQUEST or something.

 

So mr_response can have these values: PENDING, REQUEST, ACCEPTED, DECLINED, RESCHEDULE