Table design question

  • Thread starter Thread starter Damon Heron
  • Start date Start date
D

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!
 
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?
Click to expand...

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
 
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.
 
Damon said:
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.
Click to expand...

There seems to be some redundant information in your spec. 'Type'
doesn't appear to be related to group membership so I'll ignore it for
reasons of clarity. The columns skill_ID and group_ID add nothing while
making things less readable, so I'll just go with their respective name
columns. Choosing primary key columns is based on matters beyond the
scope of this thread, so I'll use NOT NULL UNIQUE in all cases. I'm
omitting basic validation (disallowing zero-length strings, etc).

Here is my proposed mini-schema (VBA code to create this structure
follows the SQL code). If it is not what you had in mind, please post
back with an amended schema:

CREATE TABLE Skills (skill_name VARCHAR(20) NOT NULL UNIQUE)
;
INSERT INTO Skills (skill_name) VALUES ('Archery')
;
INSERT INTO Skills (skill_name) VALUES ('Laser Guns')
;
CREATE TABLE Personnel (
personnel_ID INTEGER NOT NULL UNIQUE,
personnel_name VARCHAR(35) NOT NULL UNIQUE,
skill_name VARCHAR(20) NOT NULL
REFERENCES Skills (skill_name)
ON DELETE NO ACTION
ON UPDATE CASCADE,
UNIQUE (personnel_ID, skill_name)
)
;
INSERT INTO Personnel (personnel_ID, personnel_name, skill_name) VALUES
(1, 'Jones', 'Archery')
;
INSERT INTO Personnel (personnel_ID, personnel_name, skill_name) VALUES
(2, 'Smith', 'Archery')
;
INSERT INTO Personnel (personnel_ID, personnel_name, skill_name) VALUES
(3, 'Adams', 'Laser Guns')
;
INSERT INTO Personnel (personnel_ID, personnel_name, skill_name) VALUES
(4, 'Burns', 'Laser Guns')
;
CREATE TABLE Groups (
group_name VARCHAR(30) NOT NULL UNIQUE,
skill_name VARCHAR(20) NOT NULL
REFERENCES Skills (skill_name)
ON DELETE NO ACTION
ON UPDATE CASCADE,
UNIQUE (group_name, skill_name)
)
;
INSERT INTO Groups (group_name, skill_name) VALUES ('Marine laser
Guns', 'Laser Guns')
;
CREATE TABLE Members (
personnel_ID INTEGER NOT NULL,
skill_name VARCHAR(20) NOT NULL,
FOREIGN KEY (personnel_ID, skill_name)
REFERENCES Personnel (personnel_ID, skill_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
group_name VARCHAR(30) NOT NULL,
FOREIGN KEY (group_name, skill_name)
REFERENCES Groups (group_name, skill_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
UNIQUE (personnel_ID, group_name)
)
;
INSERT INTO Members (personnel_ID, skill_name, group_name) VALUES (3,
'Laser Guns', 'Marine Laser Guns')
;
INSERT INTO Members (personnel_ID, skill_name, group_name) VALUES (4,
'Laser Guns', 'Marine Laser Guns')
;

VBA to recreate the above in a new .mdb file:

Sub members()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

.Execute _
"CREATE TABLE Skills (skill_name VARCHAR(20)" & _
" NOT NULL UNIQUE);"
.Execute _
"INSERT INTO Skills (skill_name) VALUES ('Archery');"
.Execute _
"INSERT INTO Skills (skill_name) VALUES ('Laser" & _
" Guns');"

.Execute _
"CREATE TABLE Personnel (personnel_ID INTEGER" & _
" NOT NULL UNIQUE, personnel_name VARCHAR(35)" & _
" NOT NULL UNIQUE, skill_name VARCHAR(20)" & _
" NOT NULL REFERENCES Skills (skill_name)" & _
" ON DELETE NO ACTION ON UPDATE CASCADE," & _
" UNIQUE (personnel_ID, skill_name));"
.Execute _
"INSERT INTO Personnel (personnel_ID, personnel_name," & _
" skill_name) VALUES (1, 'Jones', 'Archery');"
.Execute _
"INSERT INTO Personnel (personnel_ID, personnel_name," & _
" skill_name) VALUES (2, 'Smith', 'Archery');"
.Execute _
"INSERT INTO Personnel (personnel_ID, personnel_name," & _
" skill_name) VALUES (3, 'Adams', 'Laser Guns');"
.Execute _
"INSERT INTO Personnel (personnel_ID, personnel_name," & _
" skill_name) VALUES (4, 'Burns', 'Laser Guns');"

.Execute _
"CREATE TABLE Groups (group_name VARCHAR(30)" & _
" NOT NULL UNIQUE, skill_name VARCHAR(20)" & _
" NOT NULL REFERENCES Skills (skill_name)" & _
" ON DELETE NO ACTION ON UPDATE CASCADE," & _
" UNIQUE (group_name, skill_name));"
.Execute _
"INSERT INTO Groups (group_name, skill_name)" & _
" VALUES ('Marine laser Guns', 'Laser Guns');"

.Execute _
"CREATE TABLE Members (personnel_ID INTEGER" & _
" NOT NULL, skill_name VARCHAR(20) NOT NULL," & _
" FOREIGN KEY (personnel_ID, skill_name)" & _
" REFERENCES Personnel (personnel_ID, skill_name)" & _
" ON DELETE CASCADE ON UPDATE CASCADE, group_name" & _
" VARCHAR(30) NOT NULL, FOREIGN KEY (group_name," & _
" skill_name) REFERENCES Groups (group_name," & _
" skill_name) ON DELETE CASCADE ON UPDATE" & _
" CASCADE, UNIQUE (personnel_ID, group_name));"
.Execute _
"INSERT INTO Members (personnel_ID, skill_name," & _
" group_name) VALUES (3, 'Laser Guns', 'Marine" & _
" Laser Guns');"
.Execute _
"INSERT INTO Members (personnel_ID, skill_name," & _
" group_name) VALUES (4, 'Laser Guns', 'Marine" & _
" Laser Guns');"

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
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.
Click to expand...

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.
Click to expand...

Three surely ...

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

B Wishes

Tim F
 
Back
Top