Database Relationships (Arrays)

bringmetolife

Hi. I will try to explain my dilema.

I have a music site with the following tables I want to make:

Albums
- albumid
Artists (primary key)
- artistid (primary key)

I want to be able ot list multiple artists with albums. So If I had an album called "The Calling" I want to list it with the band The Calling and Some Other Band, and Some Other Band maybe...

I heard a rumor that it was faster to use an array field in each table to link them up. Like in the albums table I have a field called albums_artistarray and in that field i have the numerical id number of each artist in my database i want to list with that album delimited by a ; or so. (example: 1;3;5). The xample would then list three artists with that album. I would also have a similar array in the artists table called artists_albumarray (this would list an array of each albumid this artist is listed with in the same way). Then I can use this array whenever I need to look up an artist and see all their albums, or look up an album and see all th artists listed with the album. The other way I could do this relationship linking is the way I have done in the past (have an extra table called artisttoalbums or something and have an artistid and albumid field and link them up that way). Can anyone tell me if an array would be faster? I know its a lot easier to manage as far as the database goes but i wanna make sure its the better way to go and not going to hurt me in the long run.

thanks for your feedback.

cahva

You dont need an extra table with artistid and albumid. Why not just put artistid column in the albums-table? One album for one artist, right? 🙂 I see you said many bands could have the same album but how can it be? 🙂 Maybe albums with the same name but they are still not the same albums IMHO!

So go for the relationship-way. Its much more clearer than that array way. Deleting albums could be a real pain in the *ss if you use the array way. And speedwise, that really doesnt matter..

kristianblom

You dont need an extra table with artistid and albumid. Why not just put artistid column in the albums-table? One album for one artist, right? I see you said many bands could have the same album but how can it be? Maybe albums with the same name but they are still not the same albums IMHO!

So go for the relationship-way. Its much more clearer than that array way. Deleting albums could be a real pain in the *ss if you use the array way. And speedwise, that really doesnt matter..

If he do it this way he can have many albums for one artist. Its not a one to one relationship.
But I guess that's what you meant. Just picking.

Use two tables.
Artist:
artist_id (primary_key ),
artist_name
... more of the info you want ...

Album:
album_id ( primary key ),
artist_id ( references the artist table )
... more of the info you want ...

dnszero

The easiest solution is to use a third table that maps the Artists id to the Album id.

MapAlbumArtist

AlbumID
ArtistID

Then you just add one line for each mapping you need. So it's no problem to have multiple artists associated with 1 album or vice versa. This setup allows you to keep your other talbes normalized and allows the many-to-many representation that you're looking for.

bringmetolife

Hi,

I do need to map multiple artists ot an album because sometimes you have albums that feature multiple artists (compilations). I know the MapToArtist table is on way of doing it but going that route it requires me to use that logic for lal my many relationship mapping for my database and would make a ton of tables when its all said and done as i am not just dealing with artists but also tracks to albums, articles, and downloads and many other things...

So are you all saying the array is not a good idea? If so can you please explain why one way is better than the other? I got the one explanation of how deletinga rtists could be a pain but im not sure how i see thats a pain?

I will await responses. Thanks again for your replies.

ahundiak

Unless you are planning on using some sort of custom databases then I don't see how you could quickly find the albums for a given artist without using a LIKE command and searching the entire table. So queries will be very slow.

And there will be no easy way to join the tables with arrays.

Using a relationship table is highly recommended. It's the "normal" and accepted way to implement many-to-many relations.

Go ahead and experiment with arrays if you want but I think you will find that using the third table is much easier and faster.

Where did you hear the rumor about speed from?

bringmetolife

I was told by another PHP Programmer he thought it was a better way maybe. He is self taught like I am so I wanted to find out be for we went that route if it was bad or good.

thanks for your replies.

ultraslacker

So are you all saying the array is not a good idea? If so can you please explain why one way is better than the other? I got the one explanation of how deletinga rtists could be a pain but im not sure how i see thats a pain?

Arrays are a horrible idea. Those who do not understand the relational principles of database design often mistake arrays for an efficient implementation when they are anything but.

The case against arrays is based on how a relational database treats data as a collection of essentially binary relations, tuples. SQL and indexing strategies proceed from this assumption, with indexes far more efficient on atomic values like integer datatypes than the strings of your 'arrays'. Your presumed efficiency will disappear with the string parsing the arrays force upon you.

Take your implementation, then write a query displaying albums, artist names, and articles for artist X and Y but not Z - possible, yes, but painfully ugly and inefficient compared to the relational approach.