Achieving First Normal Form

Achieving First Normal Form

Post by Roland Zumkelle » Sun, 06 Feb 2000 04:00:00



Hi,
I got one big unnormalized table with a group of columns repeating 10
times. So the column names contain numbers from 1 to 10. Now I want to
get a normalized table from this.
Has anyone experience doing this with a reasonable performance? I'd
probably prefer a solution written in pure TSQL.

Thanks in advance,

Roland

 
 
 

Achieving First Normal Form

Post by Bob Duff » Sun, 06 Feb 2000 04:00:00


Hi Roland,

from my experience this sort of "data modelling" has always been a manual
exercise.

Create the new tables required, run a SQL query to append the data to the
new table, delete old column, then add indexes if required, and ammend any
queries or code that uses the big table - if your doing normalisation
retrospectively.

Regards,

Bob
Prodata

 
 
 

Achieving First Normal Form

Post by BPMargoli » Sun, 06 Feb 2000 04:00:00


Roland,

create table Roland1
(
 PK_1 int identity (1, 1) primary key,
 c1 int,
 c2 int,
 c3 int,
 c4 int,
 c5 int,
 c6 int,
 c7 int,
 c8 int,
 c9 int,
 c10 int
)

create table Roland2 (PK_2 int identity (1, 1) primary key)
create table Roland3 (PK_2 int, seq int, c int)
go

insert into Roland1 values (1, 2, 3, 5, 8, 13, 21, 34, 55, 89)
insert into Roland1 values (1, 2, 3, 5, 7, 11, 13, 17, 19, 23)

set identity_insert Roland2 on
insert into Roland2 (PK_2)
select PK_1 from Roland1
set identity_insert Roland2 off




begin


                    ' from Roland1 '


end

select * from Roland2
select * from Roland3


Quote:> Hi,
> I got one big unnormalized table with a group of columns repeating 10
> times. So the column names contain numbers from 1 to 10. Now I want to
> get a normalized table from this.
> Has anyone experience doing this with a reasonable performance? I'd
> probably prefer a solution written in pure TSQL.

> Thanks in advance,

> Roland

 
 
 

Achieving First Normal Form

Post by Developer » Mon, 07 Feb 2000 04:00:00


Why not buy a book on database design.. this will explain
1nf, 2nf and 3nf beyond 3rd normal form is not worth
considering .. if you app is oltp ( lots of writes ) use normalisation
but if you app is DSS use a mixture of normalisation with redundant
data ( aggretted values ) for best performance.. I have built several
large scale business apps so I know what I am talking about..

One piece of advice though.. do not repeat fields in your tables it only
leads to trouble.. create a second table with a foreign key to the parent
table..

I am a microsoft cert. developer with tons of experience.. so any questions
just e-mail...


Quote:> Hi,
> I got one big unnormalized table with a group of columns repeating 10
> times. So the column names contain numbers from 1 to 10. Now I want to
> get a normalized table from this.
> Has anyone experience doing this with a reasonable performance? I'd
> probably prefer a solution written in pure TSQL.

> Thanks in advance,

> Roland

 
 
 

Achieving First Normal Form

Post by Roy Harv » Sun, 06 Feb 2000 04:00:00


I can't resist giving another version that does it all in a single
query!   8-)

SELECT Roland1.PK1,
       T1_10.colx as seq,
       CASE T1_10.colx
           WHEN 1 THEN c1
           WHEN 2 THEN c2
           WHEN 3 THEN c3
           WHEN 4 THEN c4
           WHEN 5 THEN c5
           WHEN 6 THEN c6
           WHEN 7 THEN c7
           WHEN 8 THEN c8
           WHEN 9 THEN c9
           WHEN 10 THEN c10
       END AS C
  INTO Roland2
  FROM Roland1,
       (SELECT 1 as colx UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6 UNION ALL
        SELECT 7 UNION ALL
        SELECT 8 UNION ALL
        SELECT 9 UNION ALL
        SELECT 10 UNION ALL) AS T1_10

Roy

 
 
 

Achieving First Normal Form

Post by Developer » Mon, 07 Feb 2000 04:00:00


Why not buy a book on database design.. this will explain
1nf, 2nf and 3nf beyond 3rd normal form is not worth
considering .. if you app is oltp ( lots of writes ) use normalisation
but if you app is DSS use a mixture of normalisation with redundant
data ( aggretted values ) for best performance.. I have built several
large scale business apps so I know what I am talking about..

One piece of advice though.. do not repeat fields in your tables it only
leads to trouble.. create a second table with a foreign key to the parent
table..

I am a microsoft cert. developer with tons of experience.. so any questions
just e-mail...


Quote:> Hi,
> I got one big unnormalized table with a group of columns repeating 10
> times. So the column names contain numbers from 1 to 10. Now I want to
> get a normalized table from this.
> Has anyone experience doing this with a reasonable performance? I'd
> probably prefer a solution written in pure TSQL.

> Thanks in advance,

> Roland

 
 
 

1. Achieving First Normal Form

Hi,
I got one big unnormalized table with a group of columns repeating 10
times. So the column names contain numbers from 1 to 10. Now I want to
get a normalized table from this.
Has anyone experience doing this with a reasonable performance? I'd
probably prefer a solution written in pure TSQL.

Thanks in advance,

Roland

2. Intermittant Data Type Conversion Error

3. first normal form

4. XML from Recordset in C++

5. First Normal Form Violation Problem

6. Sybase DBA- Leading Biotech Company

7. Meaning of "first normal form."

8. how to save diagram design as separate

9. First Normal Form and Matched Repeating Fields

10. HELP - ads/disads of non-first normal form databases

11. First Normal Form Violation

12. first normal form

13. 2nd Normal Form and Pdox Forms