Any excuse for "shortcut" columns in a db schema?_问答_开发者

Yesterday i noticed a foreign key column in a details table that linked directly to a customer table. This details table is just one join removed by a header table from the customer which already the proper foreign key to the customer and the detail, Bear with me.

[Cust] ---< [Header] ---< [Detail]
  |                          V
  |________ wtf? ____________|

ASCII db modelling Key:

                          V
 ---< = 1 to many,  and  _| also = 1 to many

When i pressed the table's designer on the issue he defended it by explaining that he'd be saving a join call by using this column...

IMO this saves a slow-typing, lazy sql writer from having to join one extra table at the price of denormalizing the schema. (Which of the normal forms is directly failed by this example?)

Even if using such a concept saved a dozen joins, 开发者_如何学JAVAis it ever worth it?

Was there an actual performance problem encountered that couldn't be solved by the addition of the appropriate index(es)?

If not, then introducing 'cycles' like that can lead to conflicting data in some situations, and I would avoid.

It is a classic answer of 'it depends' - one size does not fit all and what you are looking at is the eternal balance between a pure accademic approach vs a pragmatic one. Too far in either direction can produce a bad result, so sometimes you will sacrifice accademic correctness to get something to work well.

It is not possible to determine whether this case is a premature optimisation or a valid one without knowing workloads, number of queries, how often that join would / would not be used as a result of the optimisation etc.

Yes. Eliminating joins can have performance implications; queries that directly back a user interface running fast enough for that interface to be usable is a consideration that trumps design purity.

Though there's something to be said for maintaining a sort of partition between a well-normalized core schema and a set of summary tables, fed from the core tables, that back the UI.

Even if using such a concept saved a dozen joins, is it ever worth it?

The correct answer from the database designer/data modeller would be that there are situations where a CUSTOMER record can be relate to a DETAIL record without a supporting HEADER record, per business rules.

Adding foreign keys for the sake of it sabotages a data model, allowing for bad data. If there's only one DETAIL record associated to a CUSTOMER, then I'd expect a single record in the HEADER table - that's the point of a corrollary/xref/lookup table, to allow for 0+ supporting records. It also keeps queries consistent - none of this "what house is the moon in tonight?" fiasco leading to numerous queries...

Not knowing your table structure, I will point out that adding a customer id to multiple child tables is a common denormalization. From what you said, he made it a foreign key, so there is not risk to do so and a lot of potential performance benefit.

As long as there is also a foreign key to the header table, I see little issue with the design.

An experienced database person who knows the kind of queries that will be written against a table can see some of these denormalizations at design time. Likely if I have multiple customers, I will want to be able to query the detail table by customer without having to go through the header table on many occasions. This of course will depend on whether there is any information in the header table that I would want in the query. Here, we have the option to do either depending on which is the better choice for the particular query and all the constraints are in place to prevent data integrity issues. And all it cost us was a little extra disk space.

"The correct answer from the database designer/data modeller would be that there are situations where a CUSTOMER record can be relate to a DETAIL record without a supporting HEADER record, per business rules."

It was very clearly indicated that both relationships are one to many, so a DETAIL record without a supporting HEADER record is impossible.

But what IS possible is that something in the DETAIL pertains to ANOTHER customer than the one found when following the HEADER/CUSTOMER "path". Allthough perhaps unlikely, only the ones who defined the schema can answer that.