Re: Schema / SQL advise

Here's one (of many) option.

Use a many to many design by implementing a PersonnelTeam table with references to Personnel and Team.

PersonnelTeam (
TeamID,
PersonID,
Leader)

A unique constraint on TeamID,PersonID ensures a person belongs to a team only once. The Leader column can be set to signify the person as the team leader. This requires further management to enforce one leader per team (trigger could work).

This is probably the most straight forward implementation. There are a number of other ways to enforce the Team Leader rule, but this is a basic start.

Regards,

Carl

"Phil Barnes" <phil0_at_magma.ca> wrote in message news:dOsY6.3295$j6.368265_at_news...
> Hello,
>
> I'm going mental trying to figure out how I should establish
> relationships for a Team/Personnel type database. Any help or hints
> with Scenario C would be greatly appreciated.
>
> Tables:
>
> Teams - id (primary key)
> - name, description etc.
>
> People - id (primary key)
> - name, phone, email etc.
>
>
> Rules:
>
> 1. There can be any number of Teams
> 2. There can be any number of People
> 3. Each person can belong to any number of teams, and can either be a
> team member or a team leader.
> 4. A person can be a team leader on any or all of their teams, if
> they're not the leader they are a member.
> 5. Each team can have nay number of people, but only one team leader.
> 6. Teams and people can be added and deleted at any time.
>
>
> Goal:
>
> 1. Select a team and list it's members and leader.
> 2. Select a person and list their teams (and whether or not they are a
> member or leader).
> 3. Be able to delete a person or a team, and not have broken
> references.
>
>
> Options:
>
> Option A
>
> 1. Set up a field in Teams for leader that will hold a Person's ID.
> 2. Set up a field in Teams that will hold a comma delimmited list of
> Members IDs
> 3. Upon deleting a person, cycle through all Teams and delete
> references to the person's ID.
>
> Option B
>
> 1. Set up a field in People that holds a comma delimmited list of all
> Teams belonging to as a member
> 2. Set up a field in People that holds a comma delimmited list of all
> Teams belonging to as a leader
> 3. Upon deleting a team, cycle through all People and delete
> references to the deleted team's ID.
>
> Both scenarios use a whacky 'join' to marry teams and members.
>
>
> Option C - Some other ingenious idea?
>
> Thanks in advance,
> Phil Barnes
>
>
>
Received on Sun Jul 22 2001 - 01:32:02 CEST