Schema / SQL advise
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)
People - id (primary key)
- name, phone, email etc.
Rules:
- There can be any number of Teams
- There can be any number of People
- Each person can belong to any number of teams, and can either be a
team member or a team leader.
- A person can be a team leader on any or all of their teams, if
they're not the leader they are a member.
- Each team can have nay number of people, but only one team leader.
- Teams and people can be added and deleted at any time.
Goal:
- Select a team and list it's members and leader.
- Select a person and list their teams (and whether or not they are a
member or leader).
- Be able to delete a person or a team, and not have broken
references.
Options:
Option A
- Set up a field in Teams for leader that will hold a Person's ID.
- Set up a field in Teams that will hold a comma delimmited list of
Members IDs
- Upon deleting a person, cycle through all Teams and delete
references to the person's ID.
Option B
- Set up a field in People that holds a comma delimmited list of all
Teams belonging to as a member
- Set up a field in People that holds a comma delimmited list of all
Teams belonging to as a leader
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:31:46 CEST
Original text of this message
