Database design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Database design

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Database design

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Database design

Post 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.
Post Reply