Question: about redesigning database

  • Thread starter Thread starter Johan
  • Start date Start date
J

Johan

Hallo I am new to Access but a music collector with 60.000 albums (!!) asked me to make an Access database for him.
I imported his old database in one access table with too many fields like:

Artist, albumtitle, songtitle 1 up to songtitle 30

I think I have to split that table in
- table with artists
- table with albumtitles
- table with songtitles

My questions and problems:
1. When i make seperate tables for artists and albumtitles the wizard does many wrong suggestions to change the names. I have to change thousands rows in No change or blanc. How can I solve this?
2. How can I merge the 30 songtitle fields to 1 field keeping the right relations?

Kind regards,
Johan
 
Johan

If you step back from the tables and consider the entities (and their
relationships), it can help.

I suspect you have (and some of these will match the "tables" you
described):
Artists
Songs
Albums
and
Album-Song-Artist

This latter combination is how you would relate the various elements.

If you build corresponding tables, each with a unique ID, your
relation/junction table would store one row, consisting of IDs plus a
"track#" , per each combination.

In the example you gave, you'd end up with 30 rows -- the AlbumID would be
the same, and if the artist was the same for every song, the ArtistID would
be the same. However, the SongID would be different, and each row would
have a different "track#" value.

With a design like this, you could have an entry in the Songs table of
"Brahm's Lullaby", and could use that SongID in the relation table to look
up all the different albums and artists performing the Lullaby in the
collection.

Good luck

Jeff Boyce
<Access MVP>
 
Further to what Jeff said:

you need to read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC


Johan said:
Hallo I am new to Access but a music collector with 60.000 albums (!!)
Click to expand...
asked me to make an Access database for him.
I imported his old database in one access table with too many fields like:

Artist, albumtitle, songtitle 1 up to songtitle 30

I think I have to split that table in
- table with artists
- table with albumtitles
- table with songtitles

My questions and problems:
1. When i make seperate tables for artists and albumtitles the wizard does
Click to expand...
many wrong suggestions to change the names. I have to change thousands rows
in No change or blanc. How can I solve this?
 
Back
Top