 |  |  |  |  |  |  |  |
How to convince my boss that normalisation is best
Please support our Database Design advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
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.
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.
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.
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.
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
•
•
•
•
Originally Posted by Ebots 
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
Denis is Absolutely Right. Still, if his method doesn't work, I highly recommend mine.
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
15 minutes of research can save you 50% or more. forum.feodorgeorgiev.com
***Need Web Hosting?
******************
•
•
•
•
Originally Posted by 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.
Note to self... pocket cup
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.
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.
•
•
Join Date: Jul 2006
Posts: 831
Reputation: 
Solved Threads: 136
Practically a Posting Shark
•
•
•
•
Originally Posted by Asim Munir
[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)
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.
Last edited by pritaeas; Feb 19th, 2009 at 7:13 am. Reason: Forgot point.
"If it is NOT source, it is NOT software."
-- NASA
-- NASA
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?
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?
•
•
•
•
Originally Posted by DenisOxon
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
•
•
Join Date: Jul 2006
Posts: 831
Reputation:
Solved Threads: 136
Practically a Posting Shark
•
•
•
•
Originally Posted by tjuncewicz
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.
"If it is NOT source, it is NOT software."
-- NASA
-- NASA
|
Similar Threads
- Writing to an Access Database (Visual Basic 4 / 5 / 6)
- Dynamic web pages? Which will exist? (IT Professionals' Lounge)
- How long do you use your computer each day? (Geeks' Lounge)
- Java Server Pages (JSP)
Other Threads in the Database Design Forum
- Previous Thread: Boyce-Codd normal form
- Next Thread: Criticize Me, Please.
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest Database Design Posts
- Today's Posts
- Unanswered Threads
