Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: MySql DB structure help!

    Hello!

    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.

    ARTISTS
    artistid - smallint, length (6), Key (Primary), Default (NULL)
    artistname - varchar (70), Key (Mul?)
    origin - varchar (40)
    yearformed - year, length (4)
    biography - longtext
    website - varchar (40)
    interview - longtext
    artistimage - varchar (40)
    artisttype - char (1)

    RELEASES
    releaseid - smallint, length (6), Key (Primary), Default (NULL)
    reltitle - varchar, length (50)
    format - varchar, length (20)
    label - varchar, length (30)
    year - year, length (4)
    credits - mediumtext
    description - mediumtext
    artwork - varchar, length (30)
    artistname - varchar, length (70)

    SONGS
    songid - int, length (11), Key (Primary), Default (NULL)
    songtitle - varchar, length (50)
    trackno - tinyint, length (4)
    composer - varchar (80)
    performer - varchar (50)
    lyrics - mediumtext
    reltitle - varchar (50)

    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?

    Thanks so much for your help!!

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    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".
    Last edited by yellowmarker; 01-07-06 at 13:23.

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    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?

    Thanks a lot for your reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •