Denormalizing for sanity or performance?

Question

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))

Answer 1

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?

Answer 2

Some denormalization can be needed for performance (and sanity) reasons at some times. Hard to tell wihout seeing all your tables / needs etc...

But why not just build a few convenience views (to do a few joins) and then use these to be able to write simpler queries?