Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2008-03-28 : 06:18:10
|
| Hello,I am relatively new to database design but think I understand most of the basics but would appreciate some clarification on a few points. I have just been lumbered with what I believe an extremely inefficient and badly designed database and would like to improve in the most logical way.-Is it always preferable to fully normalise a database?For example, if the table held people, it could contain the house, town, country, continent that this person belonged to. At the moment the database I'm dealing with has all of these things contained in the 'people' table, and stored as text, not as a foriegn key linked to another table, which is obviously not an ideal approach and not normalised at all. In a fully normalised database, all these attributes should have their own table I.e., one for house, town etc. Now to run a query to find out all the people who lived in Europe, I would have to join all these tables together I.e. person to house, house to town, town to country, country to continent. This seems logical but perhaps a bit long winded. If this was a common query, would I solve this situation by creating a 'view' in the database? I've also read about stored queries, but from it seems the consensus now is that these are to be avoided as it mixes business logic with database management creating a brittle system that is difficult to evolve. Wouldn't a 'view' pose a similar problem though? I could write everything in the application, but wouldn't this slow queries down?I really appriciate the effort you guys put into answering all the questions posted here, thanks! My other questions are in different posts so that answers are only related to one subject. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-28 : 08:18:40
|
| >> -Is it always preferable to fully normalise a database?No. Sometimes it is more efficient to denormalise. I tend to keep data in a fairly normalised form if possible and build the denormalised tables from that so that the denormalised data is redundant.As for adresses you have some choices. Usually you don't have very good address data unless all inputs are validated against valid adresses - even then you will have exceptionsKeep the address on the people table - not usually good but simple especially for free text addresses.Probably better to keep these in a separate address table even if they aren't reformatted.Usually in this case you would also keep a validated and formatted copy of the address as well.As to how to keep the various parts of the address.I would prefer to keep a list of cities, countries, postcodes etc. and link to those.Note that this would work well for addresses that have been validated but you will have to be able to add extra entries for those that do not exist (hopefully they will be added after validation anyway).In the UK we can get a list of all addresses from the post office so it is common just to load that and give the address as a link to that. Again there will be addresses that aren't there so the facility needs to exist to add them.There is then the choice as to whether you leave the entities on this table as text or normalise it. I would go for (a bit of) normalisation but also have a copy of the text version as that is probably how it would be loaded and how updates are received. There would be an artificial key which was shared between the denormalised and normalised version.If you had a requirement to report on all the people who lived in Europe and needed this to run frequently or efficiently you would probably build a redundant table to service the query - which could be an indexed or materialised view.Remember that you need to build the system so that it fulfills the requirements but also important is that it is flexible and that it can be maintained by the staff available.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
Normalisation