Check Normalization on existing database?

Hi, What is the automatic process to check Normalization on existing database? Please Help.
more ▼

asked Jul 05, 2011 at 02:20 AM in Default

Amardeep gravatar image

1.3k 86 88 89

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

To expand slightly on Kev's excellent answer, there is no way to automate normalization starting with a database since proper normalization requires an understanding of dependencies within the data. Determining the dependcies requieres an understanding of the data and the relationship between the fields, this part cannot be automated.

Now, if you can fully specify all functional dependencies within your data then it does become possible to automate the normalization at that point. However, in almost all real world cases precisely specifying the functional dependencies is the hardest part of normalization, and putting it into a form that can be used by the automated tools can be painstaking even when you know what allt he dependencies are.

So, it is technically possible to automate it if you are willing and able to specify every functional dependency, but this really only comes up in academic/theoretical situations. As a practical matter, it is generally faster, simpler, and just as effective to do it by hand.
more ▼

answered Jul 06, 2011 at 05:59 PM

TimothyAWiseman gravatar image

15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

To add to @Kev Riley's answer, what's even better is that what may be normalized for one table may not be for another. A great example is telephone numbers: supposing that you have just American phone numbers with no extensions, they're in the format (xxx) xxx-xxxx. Is this normalized? It depends... Are you going to analyze area codes and try to derive geographical information? If so, then no, this is not normalized. But if you're just printing the telephone number on an invoice and never doing anything else with it, then you could make an argument that it is normalized.

Unfortunately, aside from some really obvious cases (like a table with Field1, Field2, Field3, Field4), normalization is a process, not a formula.
more ▼

answered Jul 05, 2011 at 08:22 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

My requirement as given below:- I have to design database diagram on existing database which have 1000 thousand tables without any foreign key constraint but using referenced column in each other related module tables so how should I start to design the diagram?
Jul 05, 2011 at 10:14 PM Amardeep
@sqlchamp - I should ask that as a different question - you'll get more relevant responses.
Jul 06, 2011 at 01:11 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

Normalization is a design consideration, and is based on organizing YOUR data for minimal redundancy and better performance.

Because it is data dependent, there is no automatic process, you have to apply knowledge and rules.

Certainly you can use the table metadata and the actual data to find possible areas of un-normalized data, for example nullable fields, null data, repeating data etc.
more ▼

answered Jul 05, 2011 at 02:23 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 05, 2011 at 02:20 AM

Seen: 1215 times

Last Updated: Jul 05, 2011 at 02:20 AM