Member Avatar for diafol

am i done?.. Nooo its just the database design we ve dealt with... that too not completely coz m thinking of an additional feature to add up.
as i said earlier a user will belong to a certain group and only his group's events are visible to him, i want users to first approve that he belongs to some specific group...
suggestions are welcome...

Post your own suggestions and we'll have a look at them and advise accordingly.

ok here is what i am looking for and the schema i came up with so far..
The event calendar is supposed to have following featuers-

1) events may be normal as well as recurring. A recurring event is not necessarily repeated only on conventional methods like monthly or weeky but can also be like first tuesday of every month etc..

2) A mechanism to override a specific instance of recurring event.

3) There will be a groupadmin who, when publishes an event will be visible to every user belonging to that group. Additionally groupadmin can make a subgroup including only a few members such that events published in that subgroup will be visible only to them. A user can opt to agree or refuse to be a part of subgroup.

4) A normal user can make his event public by proposing it, which when approved by groupadmin, will be visible to other users belonging to same group.

5) Resource allocation like venue or some other movables wherein a user is also treated as a resource and allocated to specific events such that 1 resource can be allocated to only 1 event at a given point of time.

To this requirement, i came up with following schema but i know its not going to work for all the features i am looking for...

events_tbl
event_id
event_title
event_descr
event_start_datetime
event_end_datetime
event_author_id
event_group_id
event_subgroup_id //if group id is set we dont need to chek subgroup
event_recurring
event_recurrence_type
event_recurrence_end
recurring_event_override_flag
override_event_id
event_approved

resource_tbl
resource_id
group_id
subgroup_id //a resource can belong to more than 1 subgrup bt only 1 group

or is it better to have subgroup_id in a separate table

group_tbl
resource_id
subgroup_id

m just lost trying... as i said earlier i m a newbie to db design, need some advice to start up...

Post your own suggestions and we'll have a look at them and advise accordingly.

Member Avatar for diafol

I'll add a bit of info, but I'll leave it to others to flesh solutions:

suggestion: resource allocation : tricky!

resource_allocation table
id (PK)
event_id (FK)
resource_id (FK)

When one-off events already exist and then a repeating/recurring event is added which could cause a clash on certain dates (but not all), the priority should be given to the event which was created first. So you may want to add an "event_created" field in the events table.

To my mind, for a simple one-off event, you'd need a system that would accept a date/time and then return all resources less the ones already allocated.
In addition, repeating events, when set up and resources allocated, should possibly have all resources available initially (or possibly certain ones greyed if not available on all repeating dates in the future, but still 'selectable'), but with a report outlining the periods on which certain resources are not available, with the option to make alternative arrangements (different resources), which would mean creating overrides (new events).

Thanq for ur info ardav... i got ur point and will make a note of it..
what would u suggest for sharing of calendars within a group and user's approval for being added in a certain subgroup...
also m trying to treat users as a resource too... that way we can determine when is he/she is free/busy..
Other experts are also welcome to share their views on this..

I'll add a bit of info, but I'll leave it to others to flesh solutions:

suggestion: resource allocation : tricky!

resource_allocation table
id (PK)
event_id (FK)
resource_id (FK)

When one-off events already exist and then a repeating/recurring event is added which could cause a clash on certain dates (but not all), the priority should be given to the event which was created first. So you may want to add an "event_created" field in the events table.

To my mind, for a simple one-off event, you'd need a system that would accept a date/time and then return all resources less the ones already allocated.
In addition, repeating events, when set up and resources allocated, should possibly have all resources available initially (or possibly certain ones greyed if not available on all repeating dates in the future, but still 'selectable'), but with a report outlining the periods on which certain resources are not available, with the option to make alternative arrangements (different resources), which would mean creating overrides (new events).

no one got any suggestions to make?? strange !!!:-O

Member Avatar for diafol

Seeing as nobody has replied yet, I'll give another bit. BTW, I like '...other experts...'! - but I ain't one of them.

For your users and finding busy times, could I suggest that you DON'T treat them like a resource.

I recently updated an old timetable script for MikeGore (here somewhere on the php forum), which did exactly this - it allowed users to book meetings and add users. I think there are a few screenshots in one of the posts. Unfortunately I cleaned out my PC the other week and got rid of a bunch of old scripts, so I don't have it anymore.

If I remember correctly:

The event can be the meeting (obviously)

Have an event_user link table:

id
event_id
user_id

However, recurring events could be a problem. Unless additional date/time fields are added to the event_user to force a single event, if that makes sense.
You could add a confirm field, where the user him/herself would then have to change the field from 0 to 1. When the meeting is scheduled, the scheduler could add all users to the event (errors on those already engaged etc) and an email/private message could be sent announcing the fact that they have to confirm by a certain date.

Anyway, just a few ideas.

Seeing as nobody has replied yet, I'll give another bit. BTW, I like '...other experts...'! - but I ain't one of them.

For your users and finding busy times, could I suggest that you DON'T treat them like a resource.

I recently updated an old timetable script for MikeGore (here somewhere on the php forum), which did exactly this - it allowed users to book meetings and add users. I think there are a few screenshots in one of the posts. Unfortunately I cleaned out my PC the other week and got rid of a bunch of old scripts, so I don't have it anymore.

If I remember correctly:

The event can be the meeting (obviously)

Have an event_user link table:

id
event_id
user_id

However, recurring events could be a problem. Unless additional date/time fields are added to the event_user to force a single event, if that makes sense.
You could add a confirm field, where the user him/herself would then have to change the field from 0 to 1. When the meeting is scheduled, the scheduler could add all users to the event (errors on those already engaged etc) and an email/private message could be sent announcing the fact that they have to confirm by a certain date.

Anyway, just a few ideas.

Thanks ardav... u r an "expert" to me... n for others, i expected lot more from this community as m trying my best to help out others hence expected same...
anyway i will try this out and let u knw... thanks once again..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.