Hi everybody…
I would like to get some advices on this data model. It deals with planification.
I want to record activities involving a variable number of resources being persons, rooms, devices and so on.
I plan to set up a many-to-many relationship beetween two tables, "activities" and "resources" with a middle table named "occ":

Resources
ID varchar PK,
Label varchar,

Activities
ID varchar PK,
Label varchar,
Start timestamp,
End timestamp,
Status int,
Sequence FK, // -> sequences table

Occ
ID varchar PK,
Res varchar FK, // resources
Act varchar FK, // activities
Label varchar,
Start timestamp,
End timestamp,
Status int,

Sequences
ID varchar PK,
Label varchar,

In the "sequences" table, one may groups some related activities.
Actually, all these tables will have subtypes, not only because resources and activities are of different types, but also to allow collections to be specified instead of individual resources or activities. Collections allow to set up an activity provisionnally saying someone in a definite group will do the job, or this employee will work on task1 or task2 or task3 this day. This provisionnal state will be reflected in status.
Any idea ?

Laurent

For anyone else mildy mystified by the word planification : [French] implementation of objectives over time. I had a very quick peek at your design and came up with the following:

• Type of resource ie is ID 3 a person, a plant or a table?
• If a resource is a person how do you contact them without extra info ie email etc?
• Sequences doesn't seem to do anything except supply a name for an ID - is this correct?
• How do you sequence activities - shouldn't there be a job x can start after job y?
• How do you know how long an activity requires a resource?
• Should there be a scheduled start time?
• How do you detect conflicts ie person X is required here and here?
• Should there be priorities on activities in case of conflicts?
• Lookup table on status?
• Why call a table just Occ?

This design seems to have nowhere near enough fields to do what you're after.

Thanks. Actually, I only gave a model skeleton, because I thought it would be tedious to read the whole thing.

* Type of resource ie is ID 3 a person, a plant or a table?
-Any of these.

* If a resource is a person how do you contact them without extra info ie email etc?
-It shall be found in a subtype table of Resources.

* Sequences doesn't seem to do anything except supply a name for an ID - is this correct?
-Yes (according to what I wrote) and no. Here will be the scheduling dependancies on activities.

* How do you sequence activities - shouldn't there be a job x can start after job y?
-Yes, and maybe the delay beetween x and y is mandatory. Maybe the same resource must be involved in x and y and so on.

* How do you know how long an activity requires a resource?
-By the start/end column in Occ table. Every resource involved in an activity has at least an "Occ" record pointing to that activity.

* Should there be a scheduled start time?
-It is the start column in activity.

* How do you detect conflicts ie person X is required here and here?
-Person X will have overlapping Occ records (in case application allows creating such records). A special case is when Person X can achieve more than one activity during the same time interval. (For instance writing mails and watching TV as my wife regularly does). Then it would be a subtype of Occ.

* Should there be priorities on activities in case of conflicts?
-That's a good question. Many conflicts shall be solved by users themselves according to their rights, but it can be interesting to implement that.

* Lookup table on status?
-Hmmm… status will probably have less than ten possible values.

* Why call a table just Occ?
-Because english isn't my mother tongue and I don't know if the full name "Occupations" has the correct meaning.

*This design seems to have nowhere near enough fields to do what you're after.
-Of course.

Laurent

How would you know which sub table to look in if there is no resource type field?

This field is a timestamp which implies an internal purpose rather than user scheduling. Would it be better to use datetime? Should you also store how long a resource has actually been used?

What if there are lots of occurrences of a particular resource, let's say many users want to schedule their need for a tea pot at a particular time in the morning. How would you cope with this?

If there's nothing to limit these values then bad data will creep in. A lookup table would also be a good place to store information about what these status values mean.

Mike,
Thanks for your replies and your interest,

I think some of your questions need some explanations about my working environment. There will be only one application using the database. The reason is everyone works in that way in our little computering world (healthcare): each software has its proprietary database and shares data using communication protocols, not accessing the same database.
One more thing about the application: we don't spread SQL queries everywhere in the code. Quite all of them are automated in special classes each devoted to one particular database kind. We use a mapping library like Hibernate. In that way, we loose the coupling beetween database and application. This explain we do have control on database access even in application and don't need to enforce data integrity and consistency in the database alone.

The type is embedded in the PK. ID values are made concatening a code identifying the type table (or the object class) and an unique value: R05-1565614 for instance stands for a resource of type R05. This is a way to comply with object model identity which is class+id.

Probably. I shall see if the distinction apply to our ordinary databases.

Probably no. Since this value is subject to change very often, I think I'd better to compute it each time I need it. (BTW, I think it would be denormalized).

We are going to application domain here. There is obviously a concurrency problem to solve, and I think I can better deal with it in a middle-tier application than directly in the database, because constraints and locks seems to me giving a bad user experience. Logically, it shall be applying controls in one point only, as if it were in the database. Scheduling will be part of a user process where users don't directly insert "blindly" new activities, but ask the software to find some possibilities. When managing globally a lot of activities, they work on a GUI showing them what is already scheduled. A large part of the concurrency problem will be solved in that way: if you want the tea-pot and you're not the first one, the software will reply it's impossible today morning, or you'll directly see on your screen when the tea-pot is available.
Remains the case where two users (or more) get a proposed activity involving the same resource at the same time. When the first one validates, the resource becomes unavailable and the other proposals obsolete. We work on this, but I doubt such a problem can be managed with database constraints. If you think otherwise, please let me know.

Final users won't probably see this and will certainly not allowed to enter it. It is for internal use only. Tell me if I miss something.

Laurent