Question of Data Normalization/Performance - Opinion needed (1 Viewer)

[fpendino]

Hi,
I am redeveloping a SQL database that I started several years ago. I have actually left this company and came back. Since being back, other developers have suggested different ways of storing/pulling data. I don't necessarily agree with their suggestions. I just have a couple scenarios where I'd like to recieve some feedback and given some opinions.

We have a reporting group which provides daily, monthly, yearly..etc, reports. I originally set this db up to be very flexible as far as the type of reporting that could be possible.

Part1
I have several Employee tables, each store bits and pieces of info. I did that to try to seperate some data that is confidential and not everytime people query for employee info, do they need every piece of info. In return, this should add a little speed, as if someone performs a query on system id's, well they don't need to see their address. I think I may change this and just create one table though, as suggested by other developers. Apparently they don't like Joins, which I understand but there would usually not be more than 2-3 joins.


Part2
I take a daily snapshot of each employees Department number and extension. We don't have a problem with storage as we only have about 300 active employees. The employees do move supervisors often so that's why i capture each day. I figure when comparing this snapshot table to other tables for reports, I can matchup the dates and be able to pull their supervisor for that date.

Now, heres kind of where I have some friction. When an employee moves groups mid month, there stats start over with the new supervisor, and go away from the old supervisor.

I have worked other places where that was not the case though, and they wanted to be able have reporting where it would show stats for this employee in both of the groups, effective their transfer date. With this my current setup would be useful. I have a feeling that they may need this in the future, as well. But it has also been suggested by others to just create one employee table, and keep multiple records for one employee within this table. So, if an employee transfers groups, add another record to this employee table with their new info, and store an effective date (or similar). Then use a view which returns all active employees and group it in a way which returns the last(current) record entered for them.

To me this just sounds like it could create more problems. Also it seems like like it could slow performance quite a bit. While my idea could require a little more maintenance at times(due to more data storage), I think it would be more ideal for performance. This db is mainly used for a backend to a dynamic website. I know my current setup runs smoothly and it works. I just have to talk others into it.

Any responses are greatly appreciated!! I have attached a snapshot, showing the table layouts, just for a better idea.

Thanks,

Frank

 

[gemma-the-husky]

part 1

this scenario is one instance where a 1-1 join is a good idea
so everyone can see normal stuff, and only certain users can see the sensitive stuff

i think some other guys here have other ways to achieve tihs, but i would leave it as is, if it is working for you

part 2 - i would be inclined to try and track employee department movements within the database, rather than storing loads of snapshots.

then you can easily extract who was where at a point in time, and you can also easily allocate stats etc, based on either the current dept, or the historic dept

 

[Galaxiom]

The image is not very clear and the relationships are hard to follow. It would have been better to show the relationships window from the database as we all know what they look like and the key files are clearly defined.

I would leave the employee data separate tables partly because that is how it is now. Moreover it potentially provides the mose robust control over security because the server controls who sees the table.

However on the other point I definitely agree with your colleagues. I would record the movements and changes in department information rather than a snapshot. Snapshots will repeat the same data over and over. With 300 employees I doubt the performance difference would be noticable either way. Their method is what is typically implemented.

Your method doens't have any advantage in reporting. They can still match up the supervisor for any date by selecting the max date prior to the query date. I think they just need to talk you into it.

 

[fpendino]

Thank you for those replies. I will take these into suggestion and I understand how your opinions would be useful. If anyone else would like to have some input, feel free.

I've attached a new picture showing the tables and relationships.