I have to design database for a company. I am not able to figure out the designing of staff table
a) There will be one group
b) The group has 3 divisions - DivA,DivB, DivC
c) There are six sections - SecA, SecB, SecC, SecD, SecE, SecF
d)SecA and SecB belongs to DivA
e)SecC and SecD belongs to DivB
f)SecE and SecF belongs to DivC
At present, the section table contains fields, section_id, section_name, division_id.
The problem is, some staffs do not belong to any section they come directly under a division. Some staff work for whole group
1. staffs are employees, it has fields staff_id, name, section
Database design
Moderator: General Moderators
Re: Database design
There are at least a couple of approaches you could use.thatsme wrote:I have to design database for a company. I am not able to figure out the designing of staff table
a) There will be one group
b) The group has 3 divisions - DivA,DivB, DivC
c) There are six sections - SecA, SecB, SecC, SecD, SecE, SecF
d)SecA and SecB belongs to DivA
e)SecC and SecD belongs to DivB
f)SecE and SecF belongs to DivC
At present, the section table contains fields, section_id, section_name, division_id.
The problem is, some staffs do not belong to any section they come directly under a division. Some staff work for whole group
1. staffs are employees, it has fields staff_id, name, section
Code: Select all
[b][u]tblEmployees[/u][/b]:
EmpID
EmplLastName
EmplFirstName
...etc.
EmplOrgID
[b][u]tblOrganizations[/u][/b]:
OrgID
OrgDivID
OrgSecID
[b][u]tblDivisions[/u][/b]:
DivID
DivName
[b][u]tblSections[/u][/b]:
SecID
SecName
Another would be:
Code: Select all
[b][u]tblEmployees[/u][/b]:
EmpID
EmplLastName
EmplFirstName
...etc.
EmplOrgID [color=#008000](could be either a DivID or a SecID)[/color]
[b][u]tblOrganizations[/u][/b]:
OrgID
OrgType [color=#008000](either Section or Division)[/color]
OrgName
OrgParent [color=#008000](blank for Divisions, the OrgID of the Division, for Sections)[/color]- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Re: Database design
Given that division and sections will change, as will the allocations of staff to them you will need to use multiple tables
populate section with all six values and division with all three. Since the way in which sections relate to divisions is soft (can be broken) I would just assign them both to staff_member. Also if both staff_member.section and staff_member.division are null you can infer this to mean "belongs to all" in your application logic. Bare in mind if you are going to have staff members who belong to many sections and divisions later you'll need a very different design.
Code: Select all
staff_member
first_name
last_name
section (allow null) foreign key on staff_section.id
division (allow null) foreign key on staff_division.id
staff_section
id primary key
name unique index
staff_division
id primary key
name unique index