Database Design Question

[bjr149]

I have a question about the design of my database. I have a table full of employees. From the manager level up each person is going to have a boss. Its like 1000 people out of 15000. I wouldnt make a field for "boss" in the employees tables. Instead i would make another table with EMP-ID and EMP-BOSS. For the data effieciency and quality? correct?

[kfcSmitty]

that would save some processing time if you did it that way. because if you want to pull out just the boss for a certain group, it wouldnt have to go through all the employees to find them

[bjr149]

Well the main reason im posting this is becauae my design would have the employees table. then another table with EMP-ID and EMP-BOSS. I was figuring that you would do this becauase in reality for this database each employee doesnt have a boss. So all those values in the employee table would be null's. I was just considering data quality. Someone elses design suggested we put that field in the employees table. I dont see why you would considering in that field there will be like 10000 null values.

[FishGuy]

Although im often guilty of not following what I have been taught your design is the most 'normal', the only reason i occasionally use the bad practice is pure laziness.

[szlamany]

I have a question about the design of my database. I have a table full of employees. From the manager level up each person is going to have a boss. Its like 1000 people out of 15000. I wouldnt make a field for "boss" in the employees tables. Instead i would make another table with EMP-ID and EMP-BOSS. For the data effieciency and quality? correct?

Having BRIDGE tables is ok - seems like it's pushed pretty hard in schools today.

But having a column in the EMPLOYEE table for the BOSS ID would be ok also. Even if 10000 of them are null.

I've seem commercial applications that took the "bridge table" concept so far that there were 500 tables in a database.

That is garbage in my opinion.

It seems my users want to get at data using EXCEL and other low-level applications. If I don't keep the data-design somewhat simple, then the ability of these users is hampered. Some would say make a VIEW for the low-level application to see the data.

Let's say you had:

Employee_T table...

EmpId int
EmpName varchar(30)
BossId int

Select ET.EmpId "Employee Id"
   ,ET.EmpName "Employee Name"
   ,BO.EmpName "Boss Name"
From Employee_T ET
Left Join Employee_T BO on BO.EmpId=ET.EmpId

That's a pretty easy query to picture.

Not that the bridge table would make it that much worse...

Select ET.EmpId "Employee Id"
   ,ET.EmpName "Employee Name"
   ,BO.EmpName "Boss Name"
From Employee_T ET
Left Join EmpBoss_T EB on EB.BossId=ET.BossId
Left Join Employee_T BO on BO.EmpId=EB.BossId

I just don't see an overwhelming reason for it.

[bjr149]

Looking at all the values in a single table... Im going to refresh this every morning with new data. Are you saying then to run a refresh on the table itself (since the BOSS_ID) is manually entered, i would only run the update on in your example the EMP-ID and EMP-NAME, ANd that would work fine>?

[szlamany]

Looking at all the values in a single table... Im going to refresh this every morning with new data. Are you saying then to run a refresh on the table itself (since the BOSS_ID) is manually entered, i would only run the update on in your example the EMP-ID and EMP-NAME, ANd that would work fine>?

What do you mean by refresh?

I thought you were simply debating whether to add the BOSS_ID into the EMPLOYEE table or to have a bridge table to show the EMP_ID and BOSS_ID (as a two column table).

[bjr149]

yes thats what i am debating, im saying if i do end up going with all data fields in one single table, when i run a UPDATE, i can just run the update on the first 2 columns considering the boss is manually entered?

[szlamany]

So you are adding new employees in some bulk fashion on a daily basis.

And someone later on will fill in the BOSS ID for the new employees?

[bjr149]

yes. only thing thats going to be added "bulk" is the just employee id and emp name for example. The BOSS will manually be filled in at any time.

[szlamany]

Well - with that process going on - getting an EMPID and EMPNAME bulk loaded - that almost argues for the bridge table - a table of just EMPID and BOSSID - that gets filled in manually at some future date.

But I still see it going either way - you could have the BOSSID in the EMPLOYEE table - but will need to allow NULL on the column - which some people are hugely against.

[bjr149]

thats what i was looking to hear. This is my first project at my new job and it happens to be an enterprise wide project, and im designing the database and implenting the daily refresh. So if your in my shoes you want it to be the best possible design and quality you can. I just want to make sure if i break off the BOSS-ID it another table i will be able to query it all at the same time. Even besides the BOSS table for certain employees, there is going to be an alternate approver table thats going to just have the employee-id again and anotehr emp-id in the 2nd column incase they have an alternate approver. Theres another case where i might have 10 people out of 15000 that have an alternate approver. Im just trying to make this as solid as i possibly can. It makes me look good... Thanks for all you help.

[szlamany]

Go with the bridge table then.

Just make sure that you have the referential integrity to delete a row from this table if the employee is deleted and to also only allow a boss id that already exists in the employee table.

(It is true that all bosses are in the employee table as well - right?)

Yes - the alternate approver table seems like another bridge table...

[bjr149]

yes were using SQL server, so i just enable the cascading deletes on each spot of referencial integrity. Its the greatest thing ive ever seen.