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

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.

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.

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?

Did a quick test and setup in Access 2002 then re-did it in mySQL.

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.

Great. I'll read up on constraints and see what I can find.