Menu
I have these tables:
Projects(projectID, CreatedByID) Employees(empID,depID) Departments(depID,OfficeID) Offices(officeID) CreatedByID is a foreign key for Employees. I have a query that runs for almost every page load.
Is it bad practice to just add a redundant OfficeID column to Projects to eliminate the three joins? Or should I do the following:
SELECT * FROM Projects P JOIN Employees E ON P.CreatedBY = E.EmpID JOIN Departments D ON E.DepID = D.DepID JOIN Offices O ON D.officeID = O.officeID WHERE O.officeID = @SomeOfficeID In application programming I "Write with best practices first and optimize afterwards", but database administrators are always warning about the cost of joins.
818
You want to start designing a normalized database up to 3rd normal form. As you develop the business logic layer you may decide you have to denormalize a bit but never, never go below the 3rd form. Always, keep 1st and 2nd form compliant. You want to denormalize for simplicity of code, not for performance.
Normalization is useful when your data has varying scales and the algorithm you are using does not make assumptions about the distribution of your data, such as k-nearest neighbors and artificial neural networks. Standardization assumes that your data has a Gaussian (bell curve) distribution.
Consequently, you can't be "over-normalized" or "under-normalized". Having said that, normalization has a performance cost. Some people elect to denormalize in various ways to improve performance. The most common sensible denormalization is to break 3NF and include derived data.
Normalize till it hurts, then denormalize till it works
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
