Help designing a database

[Jackie]

I want to create a database with for my music collection.

The information I would like to store is:
Artist
CD Information
Tracks (that are on the version)
Songs
Versions of songs

So for example:
Madonna
Like a Prayer
1.
Like A Prayer
Album Version

It is possible that the track/song/version has a different
artist to the actual CD (i.e. various cds).

I would also like to put who wrote/produced/remixed the
relevant song/version.

I've been trying to sort out the relationship/tables...can
anyone help?

 

[kevin watkins]

Jackie,

What you want is to draw this out on paper to start.
Determine what you want to be able to sort the information
by. Most databases can generate multiple reports that
provide different infomation. The key that I have found
is that the Tables need to only contain information that
is necessary. Otherwise you create more work for yourself
to keep the info up to date.

I would probably have the following tables:
1. Artist
2. Label (or Prducing company like RCA)
3. CD (this would be the largest table. it would contain
all the info about the CD such as release, date, tracks
etc.)
4. Genre

You could have even more. The reason that you want to set
up the tables like this is so you don't have to duplicate
information. If you have 10 CD's of the same artist then
you only need to list the artist once. If you have 30
publishing companies you only need that info once. That
is the joy of Relational Databases. You don't need to
store the same info over and over. You just simply
establish a relationship from one table to the other.

So the key is to establish up front what you want to get
out of the database. Once that is established then you
can determine what you need to put into it. The less info
in each data table the easier it is to manage. This also
makes it easier to change in the future. I hope this gets
you off to a start.

Sincerely,

Kevin

 

[Jackie]

Thanks for pointing me in the rigth direction.

You say that the tables should not contain duplicate data -
I have a query then. As I am a collector of music I
often have different version of the same songs. So for
example I can have the following:

CD: Example CD1
Tracks: 01 song1 album version
02 song1 remix
03 song1 edit
....etc

So if I put the tracks in the same table as CD - I will be
dupicating the song.

I also would like to say who wrote the song, would you
suggest putting this in the cd table? or a song info table?

Hope this makes some sense...and thanks again for your
earlier reply.

Jackie