Printable View
Hey guys,
Im setting up a couple of database driven websites and starting to work out the tables I want.
SInce college I`ve always normalised before creating the databases but I seem to remember hearing something last year that there is a school of thought that one big table is a lot better than having lots of little tables?
Whats everyones views on this?
How far down do you normalise?
It depends on what you want to use the DB for - OLTP databases tend to be highly normalised , but OLAP ones woudl be less normalised.
IIRC (again, I haven't done this in detail since Uni) Normalisation optimises your DB for the minimum disk space (since you don't have duplicated data), and helps prevent update errors (since you don't have to remember to update lots of duplicates of the same data) but denormalising (not normalising) can be better for write and read performance.
Ie, if the DB doesn't have to do a join, it's going to be able to give you a result set quicker.
Edit - I've just thought about that, and I think denormalising would probably only improve read performance. A good SQL textbook should have a chapter on this, but also consider that on large tables, indexes, CPU speed and memory will probably have more of an impact than weather you normalise or not. So there's an argument for designing your tables in a way that's easy to understand and code against, and just buy a shinier server to improve performance.
It has been a LONG time since I studied this, but IIRC, normalising gives more flexible4 structure and makes it easier to incorporate changes later. If you aren't normalising you are losing many of the benefits of an RDBMS.
Not everything should be normalised however - if you are running a datawarehouse , then you tend to denormalise for better query performane of the 'ing big tables that make up a DW :)Quote:
Originally Posted by peterb
The Design of Software - Database denormalisation - when, why and how?
A data warehouse is a good example of where you might be happy not to normalise, or possibly de-normalise tables to archive data.
It will depend on what data you are storing for example I cannot imagine a forum db been in one table user accounts and posts. It also worth remembering a perfectly normalise design might be impractical to implement (complexity/time).