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 Roy Harv » Sun, 06 Feb 2000 04:00:00


BP,

Or perhaps, using your table and data as a starting point, something
like:

create table T1_10 (colnbr int)
GO

INSERT T1_10 values(1)
INSERT T1_10 values(2)
INSERT T1_10 values(3)
INSERT T1_10 values(4)
INSERT T1_10 values(5)
INSERT T1_10 values(6)
INSERT T1_10 values(7)
INSERT T1_10 values(8)
INSERT T1_10 values(9)
INSERT T1_10 values(10)
GO

SELECT Roland1.PK1,
       T1_10.colmng as seq,
       CASE T1_10.colmng
           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, T1_10

select * from Roland2

Roy

 
 
 

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. Estimate average column size.

3. first normal form

4. Help with querying a recordset...

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

6. DELPHI/ Contract/ Immediate

7. First Normal Form and Matched Repeating Fields

8. Help - How do I print paradox table definitions???

9. First Normal Form Violation

10. Meaning of "first normal form."

11. First Normal Form Violation Problem

12. first normal form

13. 2nd Normal Form and Pdox Forms