Very complex design?

[George]

Dear friends,

I need to create a database for the structure of my Department.

The department is governed by a Director and under the Director we have:
Sectors - subdivided into sections, Divisions - subdivided into Sectors -
Sectors are divided into Stations and Stations are further divided into
substations, substations into subsubstations etc.

I need to record the current employees for each one of the above, e.g.
Sector A has a Supervisor, Sector A has two sections (section A and Section
B), Section A has two employees class A and 1 employee class B. Then to
create the new structure which will be more or less similar to the above
(perhaps more stations will be under each division). I need also to have
some statistics about the current and the proposed employees for each one of
the above.

Any ideas of how can I start thinking?

Thanking you in advance

GeorgeCY

 

[Allen Browne]

Put all the entities on the one table: departments, divisions, sectors, and
so on.

Fields will be like this:
- EntityID AutoNumber primary key
- EntityName Text unique name for this entity.
- ParentID Number relates to EntityID in another record.

Enter a record for the main business name, and not the EntityID assigned.

Enter a record for each department, using the EntityID of the man business
as the ParentID.

Enter a record for each division, using the EntityID from the relevant
department's record as its ParentID.

This structure allows you to nest entities to any depth.

You probably already have a table of employees, with an EmployeeID primary
key. Now you can assign employees to any entity in a 3rd table that has
fields like this:
- EntityID Number the entity the person works for
- EmployeeID Number the employee who works there.
- StartDate Date/Time when the employee started with this entity
- EndDate Date/Time when the employee finished with this entity
(Blank if current.)
- Fraction Number Double, formated percent. (1 = full time, 0.5
= half time.)

Since all the entities are in the one table, you have no problem with the
foreign key from this table to any sector or station or division.

This structure is very simple to build, but can be a bit more complex to
query. SQL (the query language in Access) is not good with nested
structures, and there is always a chance of infinite recursion (where a
record is indirectly its own parent in some previous generation.)

 

[George]

Dear Allen,

Thanks a million for your help. Yes, you are right - I will end up with an
infinite situation, i.e. perhaps 12 levels of sub sub sub divisions, which if
I will need to create many selfjoins (entityId linked each time to ParentId)
- Am I right?

Thanks again

Ο χÏήστης "Allen Browne" έγγÏαψε:

 

[Allen Browne]

In terms of creating the relationship, there is only one. To create it, you
just drag a 2nd copy of the table into the Relationships window, and drag
Entity.ParentID onto Entity_1.EntityID

In terms of mining the relationships, yes it gets quite involved. There are
SQL solutions such as these by Joe Celko:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

Alternatively, you may find it easier to set a finite limit to the nesting,
and use VBA to verify that any entry can be resolved in (say) 12 levels at
most.

More about this kind of struture:
http://allenbrowne.com/ser-06.html
http://www.mvps.org/access/modules/mdl0027.htm

 

[George]

Thanks again,

Your valuable help is highly appreciated.

GeorgeCY

Ο χÏήστης "Allen Browne" έγγÏαψε: