Re: Schema / SQL advise

Thanks all!

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:13 CEST