Hello, I'm trying to design a database to apply the following rules:

- There will be several Workgroups.
- Each Workgroup belongs to a Section and to a Division
- Division will be either 1 or 2, whatever the Section
- Some sections may have subsections
- The may be several people in each Workgroupd (at least 1) and each of those has a "job" (jobs can only be of 3 types)
- Stuff that will be done with this data:
--- display a list of all WGs (organized by Section, subsection and division)
--- display information about each Workgroup (people, etc)
--- display a list of all people
--- display information about the people (which WGs do they work for)
--- display a list of all sections

Here's an example of the relations between Section/Subsection/Division/Workgroup (the list of WG could be shown this way). Note that the first two sections have not subsection.

Tables

These are the tables and structure I've designed so far.

workgroup (workgroup_id, title, section_id, sub_section_id, division, etc)

workgroupPeople (workgroup_id, people_id, job)

section (id, etc)

subsction (id, section_id, name)

people (people_id, etc)

- "etc" means data like title, name, text

- No table for division: there are only 2 possible, numeric values, and there's little chance that that will change in the near future.
- No table for jobs: there are only 3 possible values (it's either P, T or PT) (stored as numeric values that are changed in the application code), also there's very little chance that that will change in the near future.
- Section (4 possible values) and subsection (2 possible values) are bugging me. These are both numeric (although subs do have a title) and there's not a big probability that this will change, but I think the system should offer that possibility.


What I want to know is people's opinion about this design (including the stuff that I've chosen not to use a table for). Any ideas and/or sugestions are welcome. If you need some more info on the data or what needs to be done with it, or if you need me to explain something in another way, please say so.

Thanks in advance.

I would create a single data table to hold all types of organizations (company, division, section, branch, workgroup, etc.) In that table I would have a foreign key from the org_type table (which just stores the list of org types like "company", "division", etc.)

Table: ORG_TYPE
----------
ORG_TYPE_ID (pk)
ORG_TYPE_NAME
ETC.

Table: ORG
----------
ORG_ID (pk)
ORG_TYPE_ID (fk)
ORG_NAME
ETC.

Table: PERSON
----------
PERSON_ID (pk)
PERSON_NAME
ORG_ID (fk)
ETC.

Sounds like a homework assignment to me!

I would create as generic a model as possible and then apply constraints.

Section()
Subsection()
Division()
Workgroup()
WorkgroupPeople()
Person()

Many of the constraints mentioned can be built into the table definitions.