Member Avatar for canalper

hi friends
i do organization schema for my company. i make a simple design of org. sch.
like ;
organization_schema=> org_job_ıd ,bigger_org_job_id,job_id(for example more than one department have computer enginner)
job=>job_id,job_name and also employees have relation with organization_schema
than my problem is i need to history of organization_schema
for it i add a table like;
org_history=>org_id,begin_date,finish_date
and also add column to organization_schema which name org_id
can u look and advice for better way
thanks

  • 2 Contributors
  • 1 Reply
  • 107 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by bigakis
Member Avatar for bigakis

I am not sure that I understood the problem and the schema.
A suggestion is to separate completely organization and job mapping. A table is needed for organization schema that is a self join like this:
Organization
*org_id
org_name
org_parentid.

This whay each organization level/position belongs to one and only one position (i.e IT Manager is under IT Director which is under CTO...). Quite logical i think.
You can keep your job table and you need to create a job mapping table to assign jobs to organization levels/positions:

organization_jobs
*orgjob_organizationid
*orgjob_jobid
*orgjob_validfrom
orgjob_validto (care should be taken to disallow overlaping dates)
orgjob_percent (if you need to know how much of the time will be spent to each org.position)

This assumes that the organization never changes, but can be expanded for example with a new department.
If the organization is flexible you should also add dates to the organization table but to manage this will be a nightmare. A "programmers friendly" solution is to create o top level table to keep organization revisions like this:
organization_revision
*revision_id
revision_dtfrom
revision_dtto

Organization
*org_id
*org_revisionid
org_name
org_parentid.

organization_jobs
*orgjob_revisionid
*orgjob_organizationid
*orgjob_jobid
orgjob_percent (if you need to know how much of the time will be spent to each org.position)

When a new revision is required an utility procedure copies everything from the last revision to a new one and allows the user to make the desired changes.
It may seems that is not elegant or compatible with Codd's theory but is very effective when the time comes to write queries.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.