I have a database with about 40-50 tables. All but 5 are part of a giant hierarchy of 1:M relationships that all point back to one solitary parent (call it "Project"). Each table is joined to its direct parent using a foreign key to the parent's primary key, which is an identity field. Some branches go as many as 6 or 7 levels deep. Many of the tables have millions of rows. Nearly all queries require returning all records of a particular table that pertain to a single project. Queries normally only return fields from the single entity in question. Thus a typical query (practically all queries other than random ad hoc) looks like this:

JOIN b ON a.id=b.id
JOIN c ON b.id=c.id
JOIN d ON c.id=d.id
JOIN Project p ON d.ProjectID=p.ProjectID
WHERE p.ProjectID = 12345

As you can imagine, the farther you go down the hierarchy the worse the queries perform. I considered denormalizing on the one field only by redundantly persisting the ProjectID field throughout all levels of the hierarchy, and creating the clustered index on ProjectID in each table. This would allow me to satisfy all data requests with a single index seek. Doing so dramatically improves query performance. However, I have no idea if doing this negatively impacts the fidelity of the schema design. I'm hoping someone can provide me some insight in case I am missing something.

  • 1
    Do you have indexes on your id fields? – Simon Righarts May 17 '12 at 2:11
  • Yes, but they chose to cluster on the foreign key due to the frequency of the joins – sqlbattsman May 17 '12 at 2:12
  • 1
    This could work. I would use FK/Unique constraints to ensure that ProjectID stays the same all way down the chain of child/granchild etc. tables. – A-K May 17 '12 at 3:00
  • That could be the single best solution I can think of. But Alex should answer this, his comment says very much the same I would... – dezso May 17 '12 at 6:51

We do something similar with client id. Yes it can improve performance if you don't need to go through all those intervening tables in every query. However, and it's an important however, this is best done only if you are using a surrogate key that never changes. Otherwise a change of the project id could require a cascade of updates that affect every table and lockup the system. I suspect project_id is as unlikely to change as client Id (client name, now that's another story) and so you might be fine. But please do consider if you will have updates to the field you denormalize, Also it is critical to set up a way to make sure the tables with the denormalized fields cannot get out of synch with the main table (PK/FK relationships are good for this and you might need cascading updates set (although I personally prefer not to use them if I can help it).)

  • 1
    This is exactly what I had in mind. ProjectID is a surrogate IDENTITY field in the parent table and never changes. CRUD operations are abstracted to stored procedures, and the INSERT operations programmatically enforce the correct ProjectID by grabbing it from the next level up, based on a foreign key to the direct parent's primary key. I have considered also changing the FK to use (ProjectID, ParentID) rather than just ParentID. This should help enforce referential integrity, and clustering on this with ProjectID listed first should give queries an even greater boost. Thx! – sqlbattsman May 17 '12 at 17:48

A cascading key structure would work well if you are interested in optimizing key access to the children, as the children will include the parent key. The cascading key would not be a denormalization. This also works well if you have a large amount of data and wish to partition on a high order key as now that high order key cascades all the way down the hierarchy for alignment of the partitioning scheme. Cascading keys may be a poor choice however if you are using SQL Server and require a lot of non clustered indexes as SQL Server uses the PK for the lookup into the clustered index as opposed to a RID which Oracle and DB2 would use. As always, the correct design at the physical level depends on the specific transactions you are trying to optimize at the expense of others.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.