Attn: Jeff and others - Re Question about redesigning database

[Johan]

Jeff, Thanks for your reply but still I don't know how to use the wizard to split the existing large table with 60.000 rows.

A row in the existing table has the following fields:

Keynbr
Artist
Album title + 10 album album related fields like Type(Single, EP,LP, CD), Year, Quality, Label, etc.
30 fields for the songtitles.

What is the easiest way to split the existing table with 60.000 rows?

1. The Wizard suggests thousands new names for the Artists and Albumtitles. I don't want that suggestions because they a wrong.
2. How can I merge the contents of the 30 songtitle fields to 1 field in a songtitle table and get the right relations?

Sorry for the stupid questions but I am a newbee.

Kind regards,
Johan

 

[John Vinson]

Jeff, Thanks for your reply but still I don't know how to use the wizard to split the existing large table with 60.000 rows.

A row in the existing table has the following fields:

Keynbr
Artist
Album title + 10 album album related fields like Type(Single, EP,LP, CD), Year, Quality, Label, etc.
30 fields for the songtitles.

What is the easiest way to split the existing table with 60.000 rows?

A series of Append queries.

1. The Wizard suggests thousands new names for the Artists and Albumtitles. I don't want that suggestions because they a wrong.

2. How can I merge the contents of the 30 songtitle fields to 1 field in a songtitle table and get the right relations?

Sorry for the stupid questions but I am a newbee.

Not stupid at all. This is a quite complex task!

I'd suggest creating tables as Jeff suggests, empty. You can then
create a series of Append queries to migrate the data, such as

INSERT INTO Artists ([ArtistName] SELECT DISTINCT [Artist] FROM
[bigtable];

A "Normalizing Union" query will let you populate the songs table:

SELECT AlbumID, Song1 AS Song, Artist AS Artist FROM bigtable
WHERE Song1 IS NOT NULL
UNION
SELECT AlbumID, Song2 AS Song, Artist AS Artist FROM bigtable
WHERE Song2 IS NOT NULL
UNION
SELECT AlbumID, Song3 AS Song, Artist AS Artist FROM bigtable
WHERE Song3 IS NOT NULL
UNION
<etc etc>

in the SQL window of a new Query. Base an Append query on this.

This will take a fair bit of tweaking and understanding of queries -
you're certainly in the "Deep End School of Swimming" here!

 

[HSalim]

Your new table - with Song or SongID, ArtistID and AlbumID is less than
optimal.
Placing an artist in that table is to imply that the artist performing the
song depends on the album, which would be incorrect.
Your Data Model should be:
Table:Song
Columns: SongID, SongName, Description, PlayLength, REleaseDate etc.
Table: Artists
Columns: FirstName, LastName, BirthDate...
Table:SongArtists
Columns: SongID, ArtistID, realeaseDate, Lyrics...
Table: Albums
Columns: AlbumID, AlbumName, ReleaseDate...
TableAlbumSongs
Columns: AlbumID, TrackNumber, SongID

Comments
A separate table should be used to Relate Songs to Artists because one or
more artists can be in a song.
Similarly, a song can be in one or more albums, which is why you would use a
relation table Album_Songs.
but the artist does not belong in that table.
You could Add RelaseAlbumID or ReleaseAlumName to Song if you would like to
store the Album in which the song was first released - or you could add
IsFirstRelase to AlbumSongs to serve the same purpose in a different way.
(Which one to use dependa greatly on other factors)

as for inserting data into new tables:
You could also use a loop instead of a union query: Here is the pseudocode:
dim ssql as string

for each column in BigTable
if Column.Name like "song%" then
ssql = "insert into AlbumSongs (AlbumID, SongID) Select Distinct
AlbumID," & Column.Name
ssql = ssql & " from BigTable as BT left join AlbumSongs on BT.AlbumID
= AlbumSongs.AlbunID"
ssql = ssql & " and BT.SongID = AlbumSongs." & Column.Name
ssql = ssql & " where AlbumSongs.AlbumId is null"

db.execute(ssql)
Next

Regards
HS

A series of Append queries.

Albumtitles. I don't want that suggestions because they a wrong.

2. How can I merge the contents of the 30 songtitle fields to 1 field in a songtitle table and get the right relations?

Sorry for the stupid questions but I am a newbee.

Not stupid at all. This is a quite complex task!

I'd suggest creating tables as Jeff suggests, empty. You can then
create a series of Append queries to migrate the data, such as

INSERT INTO Artists ([ArtistName] SELECT DISTINCT [Artist] FROM
[bigtable];

A "Normalizing Union" query will let you populate the songs table:

SELECT AlbumID, Song1 AS Song, Artist AS Artist FROM bigtable
WHERE Song1 IS NOT NULL
UNION
SELECT AlbumID, Song2 AS Song, Artist AS Artist FROM bigtable
WHERE Song2 IS NOT NULL
UNION
SELECT AlbumID, Song3 AS Song, Artist AS Artist FROM bigtable
WHERE Song3 IS NOT NULL
UNION
<etc etc>

in the SQL window of a new Query. Base an Append query on this.

This will take a fair bit of tweaking and understanding of queries -
you're certainly in the "Deep End School of Swimming" here!