Database structure

Anon

Hi guys and gals!

I'm currently workin' on my first bigger PHP/MySQL project. I should build a database driven site with a (fulltext) search function for a lyrics-site on the net (msuicmademe.com). Cause I'm very new in this stuff, i don't know, which structure would be the best solution between easy administration and fast search. The static site contains more than 32000 pages with almost 800 artists and 33,750 lyrics.

The current db-structure works with two tables, one artists table which contains artist, albumname, album-picture-data, artist offset and album offset in the other table. The 2nd table (titles-table) contains the songname, the tracknumber, songlyrics, comments, artist offset and album offset for the 1st table. Both tables contain an id-field which is used to find the concurrent field in the table out of the offset values.

I've really no idea, how fast/ slow a artist/ title search or even a fulltext search would become with this structure.

Do you have any recommendations for me how I could build this up?

Thanxs anyway,
Tommy.

Anon

Maybe I don't understand your database design, but what is the relation between id-field and the offset values? Is id-field a composite of artist-offset and album-offset?

Here's how I would do it - I would have three tables - artist (artist_id, ...), album(album_id, artist_id, ...), and song (song_id, album_id, ...). Create a FULLTEXT index in song on title and lyric. If the data is static, take care to cluster - mysql will cluster on primary key, so insert the data ordered by songs/albums per album/artist.

Anon

For example Artist xyz has artistOffset 3 and the first album has albumOffset 3, the 2nd albumOffset 15, the 3rd 27...., which means that the songs of the artist in the titles-tables start on id 3, the first album of the album does this too, the 2nd album songs start on id 15, the 3rd on id 27 and so on.
The other way round it works the same way: Inside the title table there is for each song an artistOffset and albumOffset value which points to the id of the artist table to determine the artist and the title of the artist.

So you would split the artist table in two tables? Mmmmh, ok, could be an idea, but I nevertheless would need to change all my offset values (then in 3 tables) if i need to insert something...

Thanks for your help, its much appreciated!

Tommy.

Anon

Okay, I understand now!

I would normalize the database design and use a primary key to distinguish entity rows (artist, album, song). I prefer to start out with a normalized design and denormalize as needed. You could combine the album and song data, as you have. You shouldn't need to change the keys (ever) with inserts.

ARTIST
artist_id
artist_name
artist_genre

ALBUM
album_id
artist_id
album_name

SONG
song_id
album_id
song_title
song_lyric

SELECT artist_name, album_title, song_title
FROM artist
JOIN album ON (artist.artist_id = $artist_id AND artist.artist_id = album.artist_id)
JOIN song ON (album.album_id = song.album_id AND MATCH(song_title, song_lyric) AGAINST $search_string);

N.B. I've not tested the match syntax.

Anon

Thanks! That helped me a lot!