Table Relationships Design

[Tom]

Via the help of the MS-Newsgroups, I have done some table relationship design in the past. Most previously, I designed
an architeture that uses a) multiple One-To-Many Relationships and b) a Many-To-Many Relationship (via Junction Table).

At this time, I am stumped again. Below are a number of tables for which I need to create the relationships.
The table names themselves (I hope) kind of describe the nature of the table content.

1. tblBoards
2. tblEmergentTasks
3. tblExperts
4. tblMeetings
5. tlbMembers
6. tblOrganizations
7. tblPlannedTasks
8. tblPriorities

Based on the information I have been given by the users, it appears that all of these tables have somehow a Many-To-Many Relationship.

For instance, the following are just some thoughts that I have come up with. It does NOT describe the actual relationships between the tables.

There will be many boards. Each board may have many organizations. Either board or organization may have many members. The boars will have different priorities. There will be planned and emergent tasks... for all, boards, organizations, members. Multipe experts might attend board sessions or regular scheduled meetings.

I truly would appreciate if someone could provide me an idea as to how they should be linked most sufficiently. Somehow I now got "stuck" on the concept of using Junction tables (because it worked out well for me last time). On the other hand, I have looked at the Northwind database sample... with a large number of tables, that db does not use Junction tables at all.

Any idea to tackle this would be appreciated!!!

Thanks,
Tom

 

[Tim Ferguson]

There will be many boards.
Each board may have many organizations.

So each Organisation has a RelatedBoard field (FK). Or, if organisations
have lots of related boards, then you need a new table called BoardsRuledBy
to manage the relationship.

Either board or organization may have many members.

Assuming that members can belong to more than one board and more than one
organisation, you need tables called MemberBelongsToBoard and
MemberBelongsToOrganisation. If the membership of boards and of
organisations is exactly equivalent, then you may need to rethink your
handling of Boards and Organisations, so they are both sub-types of
ThingsThatHaveMembers. Google on this group for more information on
SubTyping (or ask Rebecca Riordan!).

The boards will have different priorities.

That's okay: you need a table of priorities with a FK field pointing to
Boards.

There will be planned and emergent
tasks... for all, boards, organizations, members.

Okay: rethink the above organisation and consider subtyping Boards,
Organisations and Members as ThingsThatHaveTasks. Perhaps, though, there is
a difference between members' tasks and ThingsThatHaveMembers' tasks, so
you can stick with a slightly simpler strategy. Only You Know The Answer To
This, of course!

Multipe experts
might attend board sessions or regular scheduled meetings.

If Experts are a subset of Members, then this is easy to do within the
MembersAttendanceAtMeetings table.

Hope that helps


Tim F