I'm really needing some help. I'm currently trying to create a website dedicated to the local music scene in my area. I want to have a database containing as much info as possible about the artist (name, where they're from, a biography, interviews) as well as info about their releases (titles, credits, single or album). I'd also like to have tracklistings from these releases that the user can click on for individual song info and lyrics. So...
I've setup a preliminary database with three tables: artists, releases and songs.
I hope you can see what I'm trying to do. On the website you click on an artist, you get their bio and a discography. You can click on an album in the discography which will show you the tracklisting for that album. You can then click on that song to get lyrics or whatever.
So, by not really knowing what I'm doing, is my design ok? artist and release are joined by artistname and releases and songs are joined by reltitle.
Also, do I have to declare my intended lengths for varchars. Does it improve the efficiency of the DB in anyway?
you seem to be doing okay. In your SQL queries you should link the tables using an id field as opposed to a text/varchar field, so in table RELEASES change artistname to artistid, and in table SONGS change reltitle to releaseid.
re "artistname - varchar (70), Key (Mul?)" this does not need to be a key field since the releaseid field would be unique.
I assume you'd make your id fields "auto_increment".
Ah, ok. Does this mean that if I was to add a release by an artist I would have to look up the artist id in order to put it into the release instead of the artist name? While thats quite annoying is that better practise?