How can I normalize a table that has many columns

Transact-SQL
1

Hi, I'm trying to normalize this table:

+-------------------------+--------------+--------------+---------------------------------------------------+--------------------------------------+------------+------------+--------------+--------------+
| Song name               | Sheet detail | Sheet detail | MP3 path 1                                        | MP3 path 2                           | MP3 path 3 | MP3 path 4 | Sheet detail | Sheet detail |
+-------------------------+--------------+--------------+---------------------------------------------------+--------------------------------------+------------+------------+--------------+--------------+
| The Unforgiven          | some detail  | some detal   | D:\music\metallica_unforgiven.mp3                 | D:\music\apocalyptica_unforgiven.mp3 |            |            |              |              |
+-------------------------+--------------+--------------+---------------------------------------------------+--------------------------------------+------------+------------+--------------+--------------+
| Another Day In Paradise | some detail  | some detal   | D:\music\phil_collins_another_day_in_paradise.mp3 |                                      |            |            |              |              |
+-------------------------+--------------+--------------+---------------------------------------------------+--------------------------------------+------------+------------+--------------+--------------+

There will be unknown number of mp3 paths in one record. I tried

CREATE TABLE "tbl_mp3_path" (
    "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    "int_main_table_id" INTEGER,
    "txt_mp3_path" VARCHAR
)

But this seems wrong because there's more than one column with duplicate records. I'm using SQLite3 and C#.

2

Normalization is the same no matter what the type of db (SQLite, Oracle, DB/2, whatever).

1st rule for normalizing (i.e. 1NF):
All columns contain an amotic value. That is, a single value that does not need to be broken down any more to process.

So, walk thru all columns of the table and check if they meet 1NF rules:

Song Name -- looks good, single value, doesn't repeat, doesn't need broken down. But everyone knows that more than one song can have the same name. Since every entity ("table") entry must have a unique key, we'll add:

Song Identifier -- an integer value we assign to each song to give it a unique key. The first song entered will be 1, the next 2, etc., very similar to a customer number.

Sheet detail -- multiple values, so cannot be an attribute ("column") in this entity ("table"). You'll need a separate entity for sheet details. It will be linked to the main table by "Song Identifier" (thus the requirement for a unique identifier for each song).

MP3 path -- multiple values, so cannot be a single attribute in this entity. You'll need a separate entity for MP3 paths. It will be linked to the main table by "Song Identifier" (thus the requirement for a unique identifier for each song).

Add attributes ("columns") to the "Sheet Detail" and "MP3 path" entities. Then go thru those columns, checking the rules for 2NF and 3NF. When you've met those rules for all entities, it's normalized.

3

So my sql for mp3 path is correct?

4

The general approach is correct. You can clean it up a bit.

For one huge thing, every table does not need an identity column, and absolutely not as a "default" PK. No, really, it's true. You can and should have tables without an identity column, particularly for certain child tables. The PK should start with the parent's key. Or you could follow the parent key with an identity column. But for child tables, you should almost never key them only on identity. It's terrible for performance and for design.

CREATE TABLE "tbl_mp3_path"
(
"int_main_table_id" INTEGER NOT NULL,
"mp3_sequence" SMALLINT NOT NULL, /counter for each person's mp3s/
"txt_mp3_path" VARCHAR(200) NOT NULL,
PRIMARY KEY ( "int_main_table_id", "mp3_sequence" )
)

5

Hi, I'm in doubt with your example. There shouldn't be more than one column which duplicates in a table, as far as I know.

EDIT: You mean PK must be removed, I got it, thanks.

6

I don't really understand your latest comments. There are no duplicate columns in the sample table definition I gave.