How to convince my boss that normalisation is best

[Ebots]

Hi everyone,

I'm trying to convince my boss that using normalisation is the best way to design a database but he's a hacker rather than a coder and he's taking some convincing.

If we take the typical Staff\Department scenario as an example (where it would be possible for a new member of staff to not yet be assigned to a department, say - (this is similar to a problem we are trying to iron out at the moment)).

My intuition and all the text books I've ever read would say do this:

Staff
StaffId *
Name

Department
DepartmentId *
Name

StaffDepartment
StaffId
DepartmentId

However he would want to do this:

Staff
StaffId *
Name
DepartmentId (Nullable)

Department
DepartmentId *
Name

I can see his point-of-view but still want to do it the normalised way. If I can convince him that the performance of the database is better when normalised then it would sway him, but I'm no expert when it comes to indexes and table scans and the like.

Could somebody please explain if and why a normalised database performs better.

Many thanks in advance.

[sibir1us]

I dont think explaining will help. You are a smart person, and i may suggest a different solution (it does help, if used the right way):

1. Go to your boss' office and say hello to him.
2. Pick a heavy book from his desk.
3. Hit him on the head with it.

In other words, dont waste your time explaining rather simple things.

[DenisOxon]

Hi everyone,

Staff
StaffId *
Name

Department
DepartmentId *
Name

StaffDepartment
StaffId
DepartmentId

However he would want to do this:

Staff
StaffId *
Name
DepartmentId (Nullable)

Department
DepartmentId *
Name

.

Hi

First off good luck convincing him that you are correct. (You are correct)

Relational databases should contain minimum amout of repeated data for following reasons.

a) Your bosses solution will need more disk space which meabs records take longer to load etc. Try calculating extra disk space required. You know how many records and field length allocated for department.

b) Multiple department entries (your bosses method) will lead to spelling mistakes will lead to bad reporting, etc.

Your method will allow for the department to be entered once. One possible spelling mistake, one place to update, etc.

Your method leads to easier reporting, less errors, less disk space used, quicker running.

Let us know how you get on please.

Denis

[sibir1us]

Denis is Absolutely Right. Still, if his method doesn't work, I highly recommend mine.

[pty]

I dont think explaining will help. You are a smart person, and i may suggest a different solution (it does help, if used the right way):

1. Go to your boss' office and say hello to him.
2. Pick a heavy book from his desk.
3. Hit him on the head with it.

In other words, dont waste your time explaining rather simple things.

Not any old book will do; please use "A Relational Model of Data for Large Shared Data Banks" by Dr Edgar F Codd.

[Asim Munir]

Dear,

Scenario by you:

Staff
StaffId *
Name

Department
DepartmentId *
Name

StaffDepartment
StaffId
DepartmentId

Scenario of your boss:

Staff
StaffId *
Name
DepartmentId (Nullable)

Department
DepartmentId *
Name

My Conclusion:
Database design always follows business rules.
1) If one staff member can be assigned only one department then your boss is right coz ur design allows multiple departments to one staff member (violation of business rule)

2) If one staff member can be assigned multiple departments then you are right and design of your boss fails.

Note: Both designs are normalized in their respective domain of business rules. A normalized database is not always the best solutions while keeping database performance and business rules in mind.

[pritaeas]

[My Conclusion:
Database design always follows business rules.
1) If one staff member can be assigned only one department then your boss is right coz ur design allows multiple departments to one staff member (violation of business rule)

In my humble opinion, in this case you should still use the normalized version and add a unique key on the intermediate table, thus forcing one staffmember per department.

The solution with the unique also allows an easy change, should it in the future be possible for a staff member to work in two departments. Just remove the key. In the boss' version, you need to change the database AND your code.

[tjuncewicz]

It seems like there are many DBAs lurking here :-) I say that because of the dogmatic adherence to normalization at all costs, even where it doesn't make practical sense to do so. As a previous poster pointed out, the business rules should determine if a 1:1 or 1:M. Setting up the database with an extra layer for 1:M, even if only 1:1, does more than just add useless schema, but adds more complexity to the DB for hapless coders writing business logic and UI.

One poster had mentioned that doing it your way means no code change when/if 1:M is later needed, but that may be untrue if the business object is designed to expose a single entity for department as opposed to a collection (which it should if the biz rules call for that). Not to mention any UI differences such a business rule change would cause.

At some point, ya gotta weigh dogmatic "normalize for all eventualities" thinking vs practicality and simplicity. I mean, if you had a status attribute for Staff, even though only one status could be attributed to a person at a time (working, non-working, terminated), would you still use this model "just in case"? Where does the madness end?

[tjuncewicz]

Hi

First off good luck convincing him that you are correct. (You are correct)

Relational databases should contain minimum amout of repeated data for following reasons.

a) Your bosses solution will need more disk space which meabs records take longer to load etc. Try calculating extra disk space required. You know how many records and field length allocated for department.

b) Multiple department entries (your bosses method) will lead to spelling mistakes will lead to bad reporting, etc.

Your method will allow for the department to be entered once. One possible spelling mistake, one place to update, etc.

Your method leads to easier reporting, less errors, less disk space used, quicker running.

Let us know how you get on please.

Denis

I must disagree with both point a and point b IF the business rule states that a Staff can only have only one department. In fact, point a would use more space if done the poster's way. Point b would/should not happen in either case.

[pritaeas]

One poster had mentioned that doing it your way means no code change when/if 1:M is later needed, but that may be untrue if the business object is designed to expose a single entity for department as opposed to a collection (which it should if the biz rules call for that). Not to mention any UI differences such a business rule change would cause.

Absolutely true, and I agree. My point was (didn't make it clear enough I think) that the 1-1 relation does not by definition need to be made by the business logic. My preference is to enforce rules on the DB side if you can. This does not mean it's always right, only something I've encountered along the way. For example, if you think you maybe moving to a new sql server (different brand), these db forced rules may get you into trouble, because the new db doesnt support them.

[pclfw]

Simple pragmatic rule.

1) He's the boss.

2) If he's wrong refer to rule 1.