Hi all. I'm having problems deciding on a model I'm working on. I'm sure this is something that crops up a lot and probably has a simple solution but I haven't come across it myself before and its given me major headaches.

I have a users table.
users can be members of groups
users can subscribe for courses
users can enrol on lessons

we want to provide Forums
A Group may have one or more Forums
A lesson may have a Forums
A Course may have one or more Forums

So I came up with the attached diagram (lessons left off for clarity)


However the problems arise when I try to create a list of all the forums a user is entitled to view.

What do you guys think is my model right and I'm just not getting the union right or is there a better way to design the model.

Hmmm.... so a course can not exist without an associated forum? Which means that a user can not be associated to a course until a forum exists. And is that always a forum that the user is associated to via a usergroup?

Sorry I knocked that diagram up in a hurry today and didn't really check if the relationships where optional or not.

conceptually I guess a course (or a group) may exist without having a forum.

I think the relationship between Course and Forum is confusing. Is there a mandatory relationship between Course and Forum on Course ID? All i see is the optional one on Forum ID whis would violate your third premise. " A Course may have one or more Forums"

your right. The Diagram is wrong.

Course_Forums Should have the same sort of relationships as Group_Forums

Diagram currently looks more like this.

So now we have that sorted out.
The problems I am anticipating are when I try to list all the Forums a user has permissions to see. Also when I come to add Lesson_Forums or other types of forum.

Is a permission defined as the Users association to a Group and the groups association to the forum? Or, is it defined as the Users association to a course and the courses association to the forum.... or are the permissions defined independently?

A user would be allowed to see the forums for the groups they are in and for the courses they are enrolled on but not for the other groups or courses.

So, I think you have answered your question. If you can associate users to forums through groups OR courses, then you will have to get the rows from both groups and courses (union). Then, you would find the rows in common between users, courses U groups, and forums (join).

that's certainly one way to do it. My concerns are that it doesn't seem very scalable. Its quiet a complex query to get the list of user_Forums and will get even more complex if we bring on line other types of forum such as Lesson forums.

Can anyone see a better way of doing this?

Scalability and simplicity of queries were not in the original requirements. That will require a scope change.