Table design question

[Damon Heron]

This is sort of a general question- I have a list of members, each member
has a type, and each member has one, and only one skill. So I have a member
table, with ID, Name, TypeID, and SkillID. Then a TypeTable with TypeID and
TypeName. Then a SkillTable with SkillID and SkillName.

Now I want to group the members... with these rules:

1. A member can only belong to one group at a time and all of the members
of the group have the common skill.
2. A member can move from one group to another if it meets the conditions
of common skill.
3. A group can be empty.
4. New Groups can be created, some with the same skill as others
(duplicates).
5. A Group can be deleted.

Now the question: I could design this so the Member table, the type and
skill table are all that would be needed by adding a field to the member
table called GroupID. Then when a new group is created, it is added to the
Member table with ID, Name(GroupName), Type(group), SkillID, and GroupID
which equals the ID. Then members could be added by putting the Group ID in
their field.

Alternatively, I could create a new Group table that would have ID, Name,
SkillID and link it to the Member table by GroupID.

Those are the two choices as I see it. My question is, is there anything in
either that makes it preferable to the other, and would coding, maintenance,
queries be easier or harder with either?

Thanks for reading this far!

 

[Tim Ferguson]

Those are the two choices as I see it. My question is, is there
anything in either that makes it preferable to the other, and would
coding, maintenance, queries be easier or harder with either?

The main difference is that the first version is not correct. This,
roughly, is what you said:

Members (*MemberID, Skill, Group, Type)

now, the problem is that a members.skill is dependent on members.type --
in other words, for any value of members.type there is only one possible
value for members.skill. If you want this relation in 3NF, then the skill
column has to moved to Groups (where it already is).

For a more prosaic explanation, consider the situation where a member's
skill is Achery but it belongs to the MaritimeLaserGuns(1) group.
Furthermore, consider what happens when the skill required for the
ElevatedGymnastics group changes from TightropeWalking to FlyingTrapeze.
You will have to make an update to a completely different table. Bad.

Hope that helps


Tim F

 

[Damon Heron]

No, I think you misunderstood my description. A member's type is not
dependent on a member's skill. Lets say that the Type is something like
1)Probationary, 2)Annual, 3)"Group", and 4)Lifetime. The skill of the
member is NOT dependent on the type. A member could be Annual type, skill
whistling, and belong only to the whistling group. or not. My question had
to do with using one or two tables. If the Type field could be a group then
why not reference it in the one table and the ID becomes the Group ID for
the other members -like this:
ID Member TypeId SkillID GroupID
1 Jones 1 2 0
2 Smith 2 3 5
3 Adams 1 3 5
4 Burns 4 1 6
5 "GroupA" 3 3 0
6 "GroupB" 3 1 0
So the members of GroupA are MemberID 2 & 3. Member 4 is a part of GroupB.

 

[Tim Ferguson]

No, I think you misunderstood my description. A member's type is not
dependent on a member's skill. Lets say that the Type is something
like 1)Probationary, 2)Annual, 3)"Group", and 4)Lifetime. The skill
of the member is NOT dependent on the type. A member could be Annual
type, skill whistling, and belong only to the whistling group. or
not.

Sorry: I did understand the question, then got the answer all wrong. As
Jamie says below, members.skill is dependent on members.group -- in other
words, for any value of members.group there is only one possible value
for members.skill.

My question had to do with using one or two tables.

Three surely ...

This bit I got right and, AFAICT, it's still correct.

B Wishes

Tim F