f



need help on a Logical problem for my database

Hello,
I'm creating a discography for one artist on a web page. I started to
create to database, but i'm stuck.

This is the logic so far:

i have a table named "album"
i have another table called "track"

1 album contains multiple track, and 1 track can be in more then one
album. So I created the table: album_track

So far so good. Here's were i'm stuck.
How do i represent multiple disc in one album? should i create another
table called "disc" or do i insert the disc number somewhere in the
"album_track" table along the position of the track

I want to be able to create an output, for albums and albums with
multiple discs, that looks like this:

Album title
Description of the album

CD 1
------
track 1
track 2
....

CD 2
------
track 1
track 2
track 3
track 4
....


DVD
------
title 1
title 2
title 3

What do you recommend?

Thanks
marco

0
6/13/2009 6:39:57 AM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

5 Replies
514 Views

Similar Articles

[PageSpeed] 59

SM wrote:
> Hello,
> I'm creating a discography for one artist on a web page. I started to
> create to database, but i'm stuck.
> 
> This is the logic so far:
> 
> i have a table named "album"
> i have another table called "track"
> 
> 1 album contains multiple track, and 1 track can be in more then one
> album. So I created the table: album_track
> 
> So far so good. Here's were i'm stuck.
> How do i represent multiple disc in one album? should i create another
> table called "disc" or do i insert the disc number somewhere in the
> "album_track" table along the position of the track
> 
 >
<snip sample output>
 >
> What do you recommend?
> 
> Thanks
> marco
> 

That depends.  Could the same disk be in multiple albums (like the same 
track may be)?  If so, you should have a separate table.

However, in this case, it probably would not, so I would just add a disk 
number to the album-track table.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
0
jstucklex (14659)
6/13/2009 11:53:59 AM
SM wrote:

> 1 album contains multiple track, and 1 track can be in more then one
> album.

Something to consider:  frequently, an artist will record the same song
for different albums; so same title, different version. Is that
important? If so, I would think about using some sort of secondary key
in the 'track' table.

You might find the exact same version included in a "Best Of..."

If this is only a rudimentary database, this may not be important.

-- 
   -bts
   -Friends don't let friends drive Windows
0
a.nony.mous (267)
6/13/2009 1:24:40 PM
SM:

> So far so good. Here's were i'm stuck.
> How do i represent multiple disc in one album? should i create another
> table called "disc" or do i insert the disc number somewhere in the
> "album_track" table along the position of the track

Try and think in terms of data modeling. Don't just ask whether or not 
you need tables, secondary keys, fields, or whatever.

_If_ you want to see your "discs" as separate "objects" in your 
simplified world, then yes, create an entity for it in your datamodel, 
and add relationships. Such entity is required, if you want to see 
"discs" as something you want to have attributes for. Attributes are 
pieces of data, that describe your "discs".
Do "discs" have a serial number? A name? A color? A title? Perhaps even 
weight, seize or format? Do you want to keep track of the numer of 
times such "disc" has been put into a disc player? 
If you want to keep track of any of this, and use these pieces of data 
in your application, then your "discs" are going to require an entity.

> 
> I want to be able to create an output, for albums and albums with
> multiple discs, that looks like this:
> 
> Album title
> Description of the album
> 
> CD 1
> ------
> track 1
> track 2
> ...
> 
> CD 2
> ------
> track 1
> track 2
> track 3
> track 4


An ALBUM consists of 1-n DISCs, and each DISC has a unique number 
within ALBUM. Also, it's not just the ALBUM/TRACK-relationship you're 
interested in, but rather the TRACK-DISC-ALBUM-relationship.

I would recommend to model DISCS as a separate entity (which would 
result in a separate table). I would also modify the current ER-model, 
in order to make sure that ALBUMs don't carry TRACKs. DISCs do, however.


-- 
Erick
0
Erick
6/13/2009 4:53:31 PM
Erick T. Barkhuis:

> I would recommend to model DISCS as a separate entity (which would 
> result in a separate table). I would also modify the current ER-model, 
> in order to make sure that ALBUMs don't carry TRACKs. DISCs do, however.

Allow me to add, that this would also offer the possibility to easily 
add 'cover' and 'disc label' properties to the correct entity. An ALBUM 
has a 'cover' and a DISC has a 'label' (or whatever you would like to 
call that sticker on the disc).
 

-- 
Erick
0
Erick
6/13/2009 4:55:59 PM
On Jun 13, 12:55=A0pm, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
wrote:
> Erick T. Barkhuis:
>
> > I would recommend to model DISCS as a separate entity (which would
> > result in a separate table). I would also modify the current ER-model,
> > in order to make sure that ALBUMs don't carry TRACKs. DISCs do, however=
..
>
> Allow me to add, that this would also offer the possibility to easily
> add 'cover' and 'disc label' properties to the correct entity. An ALBUM
> has a 'cover' and a DISC has a 'label' (or whatever you would like to
> call that sticker on the disc).
>
> --
> Erick

Thanks Erik.
I've been thinking on my model, and i believe the 3 model entity is
the best way to go. I've also added a COVER entity coz an album could
have multiple covers (back, front, inline,etc...).

Thanks
0
7/21/2009 7:23:15 PM
Reply: