db design question

  • Thread starter Thread starter WannaKatana
  • Start date Start date
W

WannaKatana

I have a situation that I'm not sure which is the best way to model. I
have the following relationships: Department -> Group -> Team ->
Subteam My problem is that an employee may or may not be related to a
subteam but only a team and others are related more directly to the
subteam. Each team may or may not have subteams.

Some ideas:
1. Create a "dummy" subteam for teams with no subteams and relate every
employee to a subteam
2. Create recursive relationship team -> team with SubteamID in the
Team table
3. Relate each employee to the team table directly or to the subteam
table directly, whichever is appropriate, then maybe check for null
subteamID in the queries

Joel
 
All in all, I think #1 will ultimately be the easiest to work with since it
is the simplest rule.
 
I'd go with a variation on 2, where subteams and teams are the same thing,
but where subteams have a ParentTeamID.

The way you have it in 2, only 1 subteam would be possible per team. In
that case, what would be the difference between a team and its subteam?

HTH;

Amy
 
Just as a suggestion, make the following tables. This would assume that you
can come up with unique names for your departments, groups, teams, and
subteams. I suppose that similar names could just include the name of the
section above them, to distinguish (for instance, you could have Acct group
in two departments, but distinguish them by ShippingAcct and
ReceivablesAcct). But by including all of them with each employee, you can
have a team and subteam that are just place-holders, and could filter those
out for reports.

TblEmployee
EmployeeID
EmployeeName
DeptID
GroupID
TeamID
SubteamID

TblDept
DeptID
DeptName

TblGroup
GroupID
GroupName

TblTeam
TeamID
TeamName

TblSubteam
SubteamID
SubteamName
 
1. Create a "dummy" subteam for teams with no subteams and relate every
employee to a subteam
Click to expand...

Dummy records always are a sign of a Design Problem...
3. Relate each employee to the team table directly or to the subteam
table directly, whichever is appropriate, then maybe check for null
subteamID in the queries
Click to expand...

You can't do both: a FK can only relate to one table.
2. Create recursive relationship team -> team with SubteamID in the
Team table
Click to expand...

Well yes: but it doesn't solve your employee problem.

You can rethink your hierarchy:

SuperTeams (
TeamID whatever primary key,
FullName text,
// other stuff common to Teams and Subteams
)


Teams (
TeamID whatever primary key
foreign key references SuperTeams,
BelongsTo whatever foreign key references Groups
// other Team only stuff
)

SubTeams (
TeamID whatever primary key
foreign key references SuperTeams,
BelongsTo whatever foreign key references Teams
// other SubTeam stuff
)

Employees (
EmpID whatever primary key,
IsAMemberOf whatever
foreign key references SuperTeams
)

You can put constraints on the Teasm and Subteams tables to prevent their
IDs being in the other subtype table. Note the 1:1 relationships; this is
one of the few legitimate reasons for using 1:1s but I cannot see another
way of modelling what you want. Note that the FK in the Employees
references the SuperTeams table, so that it can refer to either Teams or
SubTeams.

Hope that helps


Tim F
 
Tim Ferguson said:
Dummy records always are a sign of a Design Problem...


You can't do both: a FK can only relate to one table.
Click to expand...

That's not necessarily so. You can have another field in the record that
tells you which table it relates to.

For instance, in one application, the client wanted to keep exactly the same
information for each level of a course (time in, time out, etc.) and rather
than try to have a multiply recursive structure, I created a LevelTableNames
table which tells me which table the ObjectID is referring to that is being
tracked in Session. So I could be tracking the course as a whole, a
category, topic, page, interaction, etc.

HTH;

Amy
 
, I created a LevelTableNames
table which tells me which table the ObjectID is referring to that is
being tracked in Session.
Click to expand...

No: that's not a foreign key constraint. There is nothing that the db
engine can do to protect this application-level rule unless you were using
update trigger procedures etc, which is not an option in Access.

In any case, there are perfectly good R-safe solutions for this situation.

All the best


Tim F
 
In case it makes a difference, team and subteam are the same, really
just the name at this point.

Joel
 
Back
Top