I'm not very experienced WYSIWYG designer and I came to the following problem:
I have developed a database that follows the business activity of a certain company. It includes data for products, sales, supplies, expences, etc. (It looks like the sample DB that comes with MS Access - Northwind).
At the beginning EMPLOYEES were not "on focus" in my DB - I had to track the work of just 10-15 employees, so I had a field EMPLOYEES in one of the tables.
Now It all grew bigger and bigger and I have to keep information of the whole company - 20 000 employees.
Now one field is not enough, because the company has complicated structure:
Province -> Brunch -> Department -> Section -> Employee
1) First I was thinking to make a new table EMPLOYEES with the above mentioned fields but it don't seem right - this table will contain too many duplicating data (maybe I'm wrong!!!).
2) Than I tought about making 5 different tables:
tbl.Province -> Province1, Province2, Province3 ...
tbl.RegionalBrunch -> Brunch1, Brunch2, Brunch3 ...
tbl.Department -> Department1, Department2, Department3 ...
tbl.Section -> Section1, Section2, Section3 ...
tbl.Emloyees -> Employee1, Employee2, Employee3 ...
And I think that first 4 tables should have one-to-many relationships with tbl.Employees
3) Finally I was thinking to put those tables in a different DB (Company Structure DB) and link them to my DB (Business activities DB). I think that this will reduce the size of my DB an on the other hand it will allow me to give the Company Structure DB to someone else (in Human Resourses) and ask him to keep the information about emploees current!
So my question is - Which is the better thing to do (and if I'm wrong - where exactly?)
Sorry to bother you with such stupid question but I'll be EXTREMELY GRATEFUL FOR ANY HELP PROVIDED!