database normalisation

I'm building a query and as I'm building it, I'm realizing that it'd be easier to write if some of the tables contained redundant fields; it'd save a few joins. However, doing so would mean that the database model would not be totally normalized.

I'm aiming for performance; will having a denorm开发者_如何学Calized database impede performance? I'm using SQL Server.

Thanks.

I don't know exactly what your implementation is, but it normally helps to have redundant index references, but not redundant fields per se.

For example, say you have three tables: tbl_building, tbl_room, and tbl_equipment. (An equipment belongs to a room, which belongs to a buildng)

tbl_building has a buildingID, tbl_room has a roomID and a reference to buildingID. It would save you a join if your tbl_equipment had a reference to both roomID and buildingID, even though you could infer the buildingID from the roomID.

Now, it would not be good if, for example, you have the field buildingSize on tbl_building and copy that buildingSize field to tbl_room and tbl_equipment.

In this type of situation I often find your best option is to create an indexed view that is a denormalized version of your normalized tables. This will allow you to easily query data while not creating a maintenance nightmare.

A few things to note:

• This wont work if you are using left joins
• This will slow down Insert/Update/Delete functions
• It will take up space (it's persisted).

Here is an article that goes over some of the benefits of Indexed Views.

In answer to your question; having a denormalized structure will often improve performance but it will create a maintenance nightmare.

Once you know for a fact that the joins are causing performance issues, and upgrading the hardware isn't an option, then it's either time to denormalize or if dealing with certain use cases (multiple users getting the same data e.g. for a home page of a site) start caching.

To answer your question, "will having a non-normalized database impede performance?", the answer is "it depends". Normalization is a constraint. It won't improve database performance, unless you access patterns are such that a lot of data is ignored in your queries (you have smaller result sets). But non-normalization can improve performance where you have many joins (you have bigger result sets).

Normalization does not determine performance. Normalization is about correctness and preventing certain data integrity problems.

A database in Normal Form does also help reduce design bias (a biased schema means one designed to suit some types of query better than others). In that sense it should give the best chance for the database optimiser to do its work. Denormalization means adding redundancy and in many cases that also means more storage is required for the same information - potentially impacting performance.

Denormalisation typically happens after normalisation when you have a issue, perhaps with performance.

You don't design it in up front: I can pretty much guarantee that your assumptions will be wrong and it'll be a world of pain to deal with a denormalued schema that is used in unexpected ways.

For instance, Data modification anomalies

And, perhaps I've misunderstood this last decade and a half, but aren't database engines designed to JOIN tables efficiently?

The basic purpose of normalization is to reduce redundancy of data in your tables which reduces storage wastage and inconsistency.As far as the performance is concerned,it depends on the way your database is designed.If there are too many redundancy, then checking and searching for an element in a relation will increase the search time and reduce the efficiency.On the other hand,if there is less redundancy then there won't be much effect on performance.But it is always better to have a normalized schema .