Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Question Newbie mySQL Design Question

    Greetings Follks, I was wondering if anybody could help out with this concern. Kinda new to RDBMS and not DBMS so forgive my ignorance.

    I'm designing a concert archive. Basically stores info about performances a local artist has done. Most important part is keeping track of the songs played during the shows.

    For the sake of simplicity I'm omitting a lot of fields. The tables I have created are as follows:

    tblShow * stores basic info about the show
    ---------
    id (PK)
    date
    venue
    city

    tblSong * stores a list of all the songs ever performed by the artist
    ---------
    song_id (PK)
    title
    album_name

    Each record in tblShow is going to reference quite a few of the records from tblSong. My problem/question is where are they to be stored? My first idea was create a new table called tblSetlist like this:

    tblSetlist
    ----------
    id
    song_id
    show_id

    But after a few hundred shows I thought that table is going to get huge. Am I going about this whole thing wrong? Better yet, is there a particular db topic I should be reading up on?

    Any assistance would be greatly appreciated. Thanks in advance,

    - Ish

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Newbie mySQL Design Question

    No, this approach is correct and normalised. In fact it is spookily similar to a little database I use myself to record set lists used by my band! Yes, it will grow to a few 1000 rows - probably never a million unless your artist is very busy! Even millions of rows is not "too many" in an RDBMS.

    However, I would not add that redundant "id" column in tblSetlist. My version would be:

    create table set_list( show_id references show, song_id references song,
    constraint set_list_pk primary key (show_id, song_id) );

    (That syntax may not be quite right for mySQL, I don't know mySQL).

    I would never prefix my table names with "tbl" either, any more than I would name my children "boyPeter" and "girlMary" - but that is just a personal pet hate, and it seems half the world uses "tbl" prefixes these days, perhaps because they are VB programmers rather than database designers.

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Wow Tony, thanks for making me feel smart

    Just a couple of things I need cleared up though. Where would I find more information on the "references" and constraint syntax you were working with, or rather what topic(s) would they be referred to in.

    Thanks again.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I only use Oracle, so I wrote syntax that is valid in Oracle. However, I believe it is probably standard SQL syntax supported by most other vendors. What RDBMS are you using?

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    Did a quick test and setup in Access 2002 then re-did it in mySQL.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Check the mySQL docs for details on constraint syntax. I seem to recall that in mySQL you can declare constraints but they are not enforced. This may be no longer true, I don't know.

  7. #7
    Join Date
    Jan 2004
    Posts
    4
    Great. I'll read up on constraints and see what I can find.

Posting Permissions

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