Page 1 of 1
Database design
Posted: Sat Jun 28, 2008 6:51 am
by thatsme
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
Re: Database design
Posted: Sat Jun 28, 2008 2:54 pm
by califdon
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
There are at least a couple of approaches you could use.
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
For those "organizations" that do not have a "section" ID, those would correspond to direct reporting to a division.
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]
I'm sure there are other schemas, as well, but those are the ones that come to my mind first.
Re: Database design
Posted: Sat Jun 28, 2008 10:34 pm
by Ollie Saunders
Given that division and sections will change, as will the allocations of staff to them you will need to use multiple tables
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
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.