5 ms·

Reaching the perfect database schema is only possible if you have static requirements. Out here in the real world, things change, and the more normalized your d

Reaching the perfect database schema is only possible if you have static requirements. Out here in the real world, things change, and the more normalized your database model is, the harder it is to change that to reflect your new business needs. Another opposing force is real-world performance. In most cases we're fine with a non-perfect model, we're fine with duplicated data, and we're fine with inconsistent data, because the problems they cause are much easier to deal with than performance bottlenecks. Having a slow frontpage because getting your inventory list requires a five-way join is gonna put you out of business really, really fast.

olavk 17y ago
In my experience it is easier to change a normalized scheme then a non-normalized. The redundancy and integrity issues of non-normalized schemas are really difficult to deal with when the schema has to evolve due to changing business needs. I have certainly never observed that un-normalized data should make it easier to evolve a schema - can you provide an example of when this would be the case? Regarding performance optimization, I think it is valuable to distinguish between the logical model and the physical model. Any kind of caching is a kind of redundancy, but that is not a problem if the database engine guarantees the integrity of the logical model. Relational database engines provides ways to optimize the physical storage and querying of data without compromising the logical model. For example materialized views can provide the same kind of optimization as a denormalized table in the 5-way join case - but without the integrity issues.
timwiseman 17y ago
Olavk said most of what I wanted to, but he did it better. I want to further emphasize a couple of things though: the more normalized your database model is, the harder it is to change that to reflect your new business needs. Like Olavk said,this is not my experience. It is easier to start with a normalized database and adjust. Now when adjust I sometimes have permitted exceptions to the normal form to minimize the number of code changes, but I always prefer to start normalized even if he does not stay that way. we're fine with inconsistent data, In the databases I have worked with, this is absolutely never acceptable. I know in some areas it may be, but not where you really care about being right. Every application I have been a DBA for has wanted all of its data to be correct, and if it is inconsistent you know that some of it is wrong. As a DBA I certainly could not guarantee that the data was correct, but I could normally guarantee that it was consistent and that helps.

olavk 17y ago

In my experience it is easier to change a normalized scheme then a non-normalized. The redundancy and integrity issues of non-normalized schemas are really difficult to deal with when the schema has to evolve due to changing business needs. I have certainly never observed that un-normalized data should make it easier to evolve a schema - can you provide an example of when this would be the case? Regarding performance optimization, I think it is valuable to distinguish between the logical model and the physical model. Any kind of caching is a kind of redundancy, but that is not a problem if the database engine guarantees the integrity of the logical model. Relational database engines provides ways to optimize the physical storage and querying of data without compromising the logical model. For example materialized views can provide the same kind of optimization as a denormalized table in the 5-way join case - but without the integrity issues.

timwiseman 17y ago

Olavk said most of what I wanted to, but he did it better. I want to further emphasize a couple of things though: the more normalized your database model is, the harder it is to change that to reflect your new business needs. Like Olavk said,this is not my experience. It is easier to start with a normalized database and adjust. Now when adjust I sometimes have permitted exceptions to the normal form to minimize the number of code changes, but I always prefer to start normalized even if he does not stay that way. we're fine with inconsistent data, In the databases I have worked with, this is absolutely never acceptable. I know in some areas it may be, but not where you really care about being right. Every application I have been a DBA for has wanted all of its data to be correct, and if it is inconsistent you know that some of it is wrong. As a DBA I certainly could not guarantee that the data was correct, but I could normally guarantee that it was consistent and that helps.