Denormalizing for sanity or performance?
denormalization, normalize, sql
Solution
I don't know if I would even call what you want to do denormalization -- it looks more like you just want to replace artificial foreign keys (StateId, AgencyId) with natural foreign keys (State Abbreviation, Agency Code). Using varchar fields instead of integer fields will slow down join/query performance, but (a) if you don't even need to join the table most of the time because the natural FK is what you want anyway it's not a big deal and (b) your database would need to be pretty big/have a high load for it to be noticeable.
But djna is correct in that you need a complete understanding of current and future needs before making a change like this. Are you SURE the three letter agency codes will never change, even five years from now? Really, really sure?
Problem
I've started a new project and they have a very normalized database. everything that can be a lookup is stored as the foreign key to the lookup table. this is normalized and fine, but I end up doing 5 table joins for the simplest queries. ``` from va in VehicleActions join vat in VehicleActionTypes on va.VehicleActionTypeId equals vat.VehicleActionTypeId join ai in ActivityInvolvements on va.VehicleActionId equals ai.VehicleActionId join a in Agencies on va.AgencyId equals a.AgencyId join vd in VehicleDescriptions on ai.VehicleDescriptionId equals vd.VehicleDescriptionId join s in States on vd.LicensePlateStateId equals s.StateId where va.CreatedDate > DateTime.Now.AddHours(-DateTime.Now.Hour) select new {va.VehicleActionId,a.AgencyCode,vat.Description,vat.Code, vd.LicensePlateNumber,LPNState = s.Code,va.LatestDateTime,va.CreatedDate} ``` I'd like to recommend that we denormaize some stuff. like the state code. I don't see the state codes changing in my lifetime. similar story with the 3-letter agency code. these are handed out by the agency of agencies and will never change. When I approached the DBA with the state code issue and the 5 table joins. i get the response that "we are normalized" and that "joins are fast". Is there a compelling argument to denormalize? I'd do it for sanity if nothing else. the same query in T-SQL: ``` SELECT VehicleAction.VehicleActionID , Agency.AgencyCode AS ActionAgency , VehicleActionType.Description , VehicleDescription.LicensePlateNumber , State.Code AS LPNState , VehicleAction.LatestDateTime AS ActionLatestDateTime , VehicleAction.CreatedDate FROM VehicleAction INNER JOIN VehicleActionType ON VehicleAction.VehicleActionTypeId = VehicleActionType.VehicleActionTypeId INNER JOIN ActivityInvolvement ON VehicleAction.VehicleActionId = ActivityInvolvement.VehicleActionId INNER JOIN Agency ON VehicleAction.AgencyId = Agency.AgencyId INNER JOIN VehicleDescription ON ActivityInvolvement.VehicleDescriptionId = VehicleDescription.VehicleDescriptionId INNER JOIN State ON VehicleDescription.LicensePlateStateId = State.StateId Where VehicleAction.CreatedDate >= floor(cast(getdate() as float)) ```