Hello
The action of making your database fully relational is known as
normalization. The process of normalisation usually turns data into
many small tables, with many indices joining the tables.
You would intentionally leave tables non-normalised because
- you might fear performance problems from the number of tables and
links,
Performance problems derive from production queries that require too
many time-consuming, disk intensive table joins that can be minimized
by simplifying the data structure ie not splitting tables through
normalisation.
- you want to simplify user ad-hoc reporting.
Ad-hoc reporting is about letting end users perform unstructured
queries; untrained end users can be uncertain about how to get
information from multiple related tables and can get lost in large
data structures. If end-user querying is a large part of your system,
you might want to leave the tables non-normalised, especially if the
table in question is one that users will be accessing frequently.
You can also make a table non-normalised after having already
normalising it, for any specific table on the system. The techniques
for denormalization include duplicating data, providing summary data,
splitting tables into horizontal or vertical partitions, and creating
denormalized views to simplify reporting ( a clever alternative that
leaves your normalized database intact.)
As an example of denormalization, consider the case of customer
addresses taken from the MSDN article on Data Integrity. A customer
table would typically include these attributes: name, street, city,
state, and zip code. While it is true that the city and state can be
determined directly from the zip code, and therefore you could
normalize the customer table by removing the city and state from each
customer's data, it is a common practice to leave the address
denormalized.
There are several reasons you might have for denormalizing addresses
(and this also applies to any frequently used data items on the
system):
- Addresses are used in many places (queries, reports, envelopes,
customer support screens), and denormalizing avoids adding a lot of
address reconstruction code throughout the application.
- Address-based queries without using cross indexing use a much
simpler SQL syntax.
- Address errors are limited to single customers and not across a
whole street, (or even city!)
Hope that helps
Willie
The MSDN article on Data Normalisation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxconDataNormalization.asp
A good article on Data Design
Data Design
http://216.239.39.100/search?q=cache:OoHlEgyf1QYC:www.webster.edu/~crawfodj/2810/pdf/2810ch08.pdf+%22Data+Design%22+%22normalization%22+definition&hl=en&ie=UTF-8
Google Search Strategy
://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&q=%22Data+Design%22+%22normalization%22+definition |
