Database structure and potential queries

[creole]

Update:

I'll need to add three fields to the performance table. These fields will be unique to each show. The fields are Description (a one paragraph description of each 4 artist grouping), Name (each show has a unique name based on the artists performing in it) and cost (stupid me just left this one out).

Note that the screenshot has been updated.

[DaveMaxwell]

OK, then based on your answers, this would be the layout I would assume:

Artist
- ArtistID (PK) <= Good idea to name fields like this to make it easier to match up later
- FirstName
- LastName
- ShortBio
- Full
- Photo
- Phone (only one possible phone #?)
- email

ShowType
- ShowTypeID (PK)
- TypeDescription

Venue
- VenueID (PK)
- Name
- Address
- City
- State
- Zip
- Phone
- MapImage
- Details

Performance
- PerformanceID (PK)
- PerformanceName
- PerformanceDescription
- PerformanceCost
- StartDate
- EndDate
- PerformanceTime
- VenueID (FK to VenueID on Venue)
- ShowTypeID (FK to ShowTypeID on ShowType)

PerformanceArtists
- PerformanceID (FK to PerformanceID on Performance)
- ArtistID (FK to ArtistID on Artist)
- StartDate
- EndDate

Notes
[list=1][*]If you noticed, I added another date to the Performance table. This would allow for the club dates you were talking about. I added the same for the PerformanceArtists. You can remove them if you find you don't need them.[*]You asked about foreign key definition in Access. You do that by defining relationships. If you need more help than that, please let me know.[*]For Matt. I defined the many to many table (which he doesn't need because of what the ShowType is) because different artists could be considered for different types of music. For example, I sing in a barbershop quartet. Now, my quartet could be considered for a Barbershop Night, An a Capella night, or a general night. I would think a local artist would want to be known as being flexible to be as bookable as possible, so that's why I defined that table that way. [*]Creole, I'm glad to see you're taking the time to learn to do this the right way and make yourself even more marketable. Some people would have slapped something ugly and moved on.[/list=1]

<edit>Grrrrr....Stupid building had a fire drill and missed a post in the meantime. Fixed post to meet new criteria.</edit>

[creole]

How will I be able to associate multiple artists with one particular show? Under PerformanceArtist would I then simply say "this artist is playing at the show whose ID matches '234'"?

What is the purpose of start and end date in the PerformanceArtist table? Would it not use the start and end date from each individual performance?

Thanks Dave...I appreciate your help and the help of Matt and dHTMLGod. I'm doing multiple things at once for this site. Luckily, I've got plenty of time as this event doesn't start until April 1. I'm working on the DB and the design at the same time so I'm jumping back and forth.

Thanks again.

[DaveMaxwell]

Originally posted by creole
How will I be able to associate multiple artists with one particular show? Under PerformanceArtist would I then simply say "this artist is playing at the show whose ID matches '234'"?

Correct. For every show you would add a record for each artisst performing on that show.

What is the purpose of start and end date in the PerformanceArtist table? Would it not use the start and end date from each individual performance?

It can. I added that in case someone did not play all the days for a multiple day show (ie for a five day show, artistA played days 1-2, ArtistB played days 3-4, ArtistC played all five days). If they always play all days of a show, then you don't need the fields. Now, if an artist could play days not in order (ie artistA plays days 1, 3 and 5 in the previous example), then you should probably have another table that contained the PerformanceArtist table key (in this case, an autonumber/ID field added to PerformanceArtist would be useful) and starting and ending dates. If that is not a need, then don't worry about it.

Thanks Dave...I appreciate your help and the help of Matt and dHTMLGod. I'm doing multiple things at once for this site. Luckily, I've got plenty of time as this event doesn't start until April 1. I'm working on the DB and the design at the same time so I'm jumping back and forth.

Glad I could help. I'm glad you have a little time to plan it out properly instead of slamming something in and having to fix it later....

[creole]

OK...two more questions/comments and then I'll start building this thing tomorrow.

-= I think I might have misled you talking about "shows". This whole thing is a 5 day long "event". During the event, there will be multiple shows. Each show is a self contained little world. An artist may possibly play more than once during the 5 days, but it would not be related to any other show.

-= I'll be building a CMS for this as it's so complicated. How would a potential FORM look like to enter a show? It would be great if I could input one show 4 times (once for each artist) as opposed to 4 times.

Maybe something like this:
- PerformanceID: auto-number
- PerformanceName: The amazing creole and friends (artists #3, #45, #27, #13)
- PerformanceDescription: See creole do death-defying musical stunts
- PerformanceCost: $5
- StartDate: 04/01/2002
- PerformanceTime: 9pm
- VenueID: 4 (bluebird cafe)
- ShowTypeID: 3 (club show)

Now, given the above information, and based on the info that the client has given me, I would really need to list a show for each artist (for sorting purposes). Should it be possible (programmatically), to insert a new row into the associated DB's for each artist ID?

The page that processes the form should just need to loop through each artistID and insert once for each right? Cold Fusion makes it easy to process forms but I still need to be able to decide how to set it up. I know that you might not know CF, but in general, would my description of the FORM be fairly correct?

Thanks again.

[DaveMaxwell]

OK, I have a tendency to be a little dense. Let me know if I understand you correctly:

You have a performance (more what I would call a festival) that is a collection of shows. Each artist can play on any of these shows at a given performance. Is that correct? If so, I think we're going to need to alter the db structure a tad to meet your needs

Based on my understanding, here is how I would set up the CMS:
1) Set up the performance. I guess it would be similar to what you had specified (don't need to show performanceID on the add (unless you want to display it for informational purposes)

- PerformanceName: The amazing creole and friends (artists #3, #45, #27, #13)
- PerformanceDescription: See creole do death-defying musical stunts
- PerformanceCost: $5
- StartDate: 04/01/2002
- EndDate: 04/06/2002 You're going to want to carry this UNLESS all performances are always five days long
- PerformanceTime: 9pm Will they always be the same time every day or multiple times...
- VenueID: 4 (bluebird cafe)
- ShowTypeID: 3 (club show)

2) Set up the shows for the performance and then have a series of pulldowns that will allow you to select artists to perform. You'll want to figure out the maximum number of performers that do these shows, then add one or two (just to allow for future growth)
Display Performance Info
- ShowID
- ShowStartDate
- ShowEndDate
- Artist Pulldowns


This way, you should only ever have one more form to enter than the number of shows for the event (one for the performance, one for each show in the event). Of course, you'd want a page to maintain your artists (add new, update info, delete, etc).

Let me know if my understanding is correct and this is the way you want to proceed and we'll go from there...

[creole]

Yep...you're misunderstanding me.

A show will:
a) Be one day only
b) be a few hours max.
c) Contain a grouping of 4-5 artists

In the example I gave you, "creole and friends" would be 4 total artists and would play for around 1-2 hours. It's called a "round", where each performer plays one song, then the next, and the next until the show is over. Then (at the same venue), the next group of 4-5 comes up and the process starts again. This time a different show name, performers, time, etc. Does that make more sense?

It's very likely that over this 5 days, each performer may only play once. From talking to the clients, they've said that very few performers (out of the 500 or so) play twice or more.

[DaveMaxwell]

OK, so you have an event which can last several days. Each event has several shows over the course of the event. Each show can have 1+ artist on the show. Most likely an artist will only play on one show, but they can play on more than one. I think <crosses fingers> I got it. Based on that information, here is how I would build the database(redefining whole database to make sure nothing gets missed):

Artist
- ArtistID (PK)
- FirstName
- LastName
- ShortBio
- Full
- Photo
- Phone (only one possible phone #?)
- email

ShowType
- ShowTypeID (PK)
- TypeDescription

Venue
- VenueID (PK)
- Name
- Address
- City
- State
- Zip
- Phone
- MapImage
- Details

Event - Changed terminology for my understanding
- EventID (PK)
- EventName
- EventStartDate
- EventCost
- EventEndDate
- VenueID (FK to the Venue table)
- ShowTypeID (FK to the ShowType table)

Show
- ShowID (PK)
- EventID (FK to Event)
- ShowName (probably not necessary)
- ShowDate
- ShowTime

ShowArtist
- ShowID (FK to Show)
- ArtistID (FK to Artist)

For building the CMS, I would still build it the way I described it in my previous post. Create the Event, add shows, then add the performers to the show. If you've got up to five performers per show, then when you build the show, create enough pull down boxes based on the artists table to list all the artists that are available to create the maximum number of performers planned (again add a couple to make it easier to expand in the future).

[creole]

Great...the only thing I probably won't do is use a drop-down menu for artists. Since ther ecould be around 500 artists, that's far too many for a dropdown. I might get them to print out a reference and use that to type in the number. I'll see once I start building this.

Thanks.

[DaveMaxwell]

Originally posted by creole
Great...the only thing I probably won't do is use a drop-down menu for artists. Since ther ecould be around 500 artists, that's far too many for a dropdown. I might get them to print out a reference and use that to type in the number. I'll see once I start building this.

Thanks.

Yeah, the dropdown for the artists would probably be a bad idea. You could either print out a reference (they would need to remember to print out a new one every year) OR you could do something similar to the way the smilies page works here and have a pop-up that returns a value to the calling page....

[creole]

Hmmm...that would probably be a little beyond me. Maybe I'll give it a try once I've gotten everything else take care of.

Thanks again for all of your help Dave.

[DaveMaxwell]

Originally posted by creole
Hmmm...that would probably be a little beyond me. Maybe I'll give it a try once I've gotten everything else take care of.

Good idea. Get the core functionality working, then pretty it up.

Thanks again for all of your help Dave.

Not a problem. Always glad to help a fellow Sitepointer....

[shane]

Kinda sticking my nose in late but what the hell...

Dave, I'm not sure you need the Shows for the Events. I think Creole's saying that the whole 'festival' be a number of single day Events that take place over a single week.

If I'm right adding the extra Shows will just add unrequired complexity. It gives more flexibility but as the whole event is over just a single week the simpler route may be better.

Also, if the db schema stays as your last post I think changing the ShowType table to EventType would be best as it's now used in the Event table. This'd help to avoid any confusion later on.

Assuming Dave's last schema is used, I think adding an EndTime field to the Show table could be of use (and renaming the ShowTime to StartTime) so that people knew the Show length. Or a ShowLength field could be used instead, in conjunction with the ShowTime (start time).

Also, it may be of use to add some kind of history audit to the Shows/Events tables, so that if an Event is moved to another Venue or Cancelled then this too can be listed.

[DaveMaxwell]

Originally posted by shane
Kinda sticking my nose in late but what the hell...

Not a problem, the more the merrier.

Dave, I'm not sure you need the Shows for the Events. I think Creole's saying that the whole 'festival' be a number of single day Events that take place over a single week.

If I'm right adding the extra Shows will just add unrequired complexity. It gives more flexibility but as the whole event is over just a single week the simpler route may be better.

True, but there are multiple shows per event, and the artists will only play one show.

Also, if the db schema stays as your last post I think changing the ShowType table to EventType would be best as it's now used in the Event table. This'd help to avoid any confusion later on.

Good catch!!! I missed that entirely....

Assuming Dave's last schema is used, I think adding an EndTime field to the Show table could be of use (and renaming the ShowTime to StartTime) so that people knew the Show length. Or a ShowLength field could be used instead, in conjunction with the ShowTime (start time).

Another good idea....

Also, it may be of use to add some kind of history audit to the Shows/Events tables, so that if an Event is moved to another Venue or Cancelled then this too can be listed.

Could definitely be a future enhancement (like the pop-up window to select artists. Great idea, but not key to functionality.

[platinum]

Woah this thread was interesting

I'm never, ever letting you guys look at my databases (you'd pick 'em to shreads)

[DaveMaxwell]

Originally posted by platinum
Woah this thread was interesting

I'm never, ever letting you guys look at my databases (you'd pick 'em to shreads)

Who? US????

[shane]

Originally posted by DaveMaxwell
... but there are multiple shows per event, and the artists will only play one show.

I'm not sure there is from what Creole's said. Maybe Creole can clear this point up?

Originally posted by DaveMaxwell (re: history/audit)
Could definitely be a future enhancement (like the pop-up window to select artists. Great idea, but not key to functionality.

Well, the db would work fine but as Creole stated that there's likely to be last minute changes a historty/audit trail could be fairly important to the organsisers, performers and festival goers. I guess the need for this would depend upon on the amount of last minute changes expected.

Couple of other things:

1. I think changing the name of the lookup table, ShowArtist, to ArtistShow would make it slightly more obvious as to the nature of the data in this table.
2. The ShowArtist table needs a primary key (a combination of the ArtistID and ShowID).
3. If the Show/Event tables stay the same there'd be a need for a constraint on the Event table so that a Show couldn't have overlapping dates.

[creole]

OK...I'm back in this thing. I agree with Shane, I'm building the database now and I don't really understand the difference between Event ahd Show.

Let me try this one more time. Maybe I'll get the explanation right, or at least so that you understand me.

Forget the "event" thing. Look at this as a collection of shows over 5 days. At a single slice in time, there might be 20 shows going on (at the various venues) but we'll only look at one show.

The properties of a show:
-= Each show is part of a larger category of "types of shows", where type corresponds to the listing I gave earlier. We'll consider this show we're talking to be in the "club shows" category as that's what most shows will be grouped under.

-= Each show will have a start date, time, cost, name and grouping of 4-5 performers. No need to list end time or end date as they will all end the same day they start and no one cares what time the show gets done.

Does that make more sense? I SHOULD be able to combine "Show" and "Event" from your example above Dave and be in good shape. Maybe something like this:

Performance
- PerformanceID (PK)
- PerformanceName
- PerformanceDate
- PerformanceCost
- VenueID (FK to the Venue table)
- ShowTypeID (FK to the ShowType table)

The only thing is how would I use this structure to associate an artist with a performance?

[MattR]

Creole, to avoid confusion, use consistent naming in a human-readable phrase or paragraph. Here is where discrete mathematics would come in handy, but since I can't write symbols in plantext write it in words, e.g.:
There must be at least one performer who is in a show. Each show can be performed in exactly one venue, and each venue is always :etc:

That will help a lot I think. Come up with a definitive purpose and then the DB structure will folow fluidly from that.

[DaveMaxwell]

I understand the concept of a show, but I thought you told me they were all a part of a larger 4-5 day "event". How can you keep track of which shows go with which event? Or don't you care about that?

[creole]

Thanks for the suggestion MattR.

Dave, first off, let me apologize if you think I misled you. I GREATLY appreciate your help and accept that any miscommunication was on my part.

Each show is indeed part of an "event" but a better word for event would be "category". There are 6 or 7 categories including "Legends Show" and "Club Dates". While each show is grouped in only one category, the concept of a multi-day event is not really needed in this context. It's better to look at it as a bunch of individual show which we group in certain categories.

As for keeping track of which show goes with which event, that's what the ShowType table is for.

Does that make more sense?

[shane]

Can I try?

• There's one Event that takes place over 4-5 days.
• The Event consists of one or more Shows.
• Each Show is performed in just one Venue.
• A Venue may only hold one Show at any one time (?).
• Each Show takes place only once.
• Each Show may consist of performances by one or more Artists.
• An Artist may perform in one or more Shows
• Each Show is of just one particular Type of Show.

The single Event is the 'thing' we're modelling so it doesn't need to represented in the DB.

Creole, the reason for using the ShowArtist table is that otherwise there'd be a Many-To-Many relationship between the Show and Artist tables.(ie A Show may consist of performances by multiple Artsists and each Artist may perform multiple Shows) Many-To-Many relationships can't be represented in relational DB's so a lookup table is used. The lookup table breaks 2 tables, with Many-To-Many relationship, into 3 tables and 2 One-To-Many relationships.

The Show, ShowArtist and Artist tables are then all used when you'd want to retrieve the names of all Artists for a particular Show.

If you've got Kevin Yanks book (or the full original article) then have a read of Chapter 5 as it contains a good description of creating and using lookup tables.

This article may help:
Fundamentals of Relational Database Design

As for keeping track of which show goes with which event, that's what the ShowType table is for.

I'm a bit confused by this. Do you regard ShowType as an Event or just a particular type of Show.

I've ceated in ER diagram for you, I'll post it when I get back home after work. It may help us in understanding the tables and relationships.

[creole]

Shane...first off, a MUCH better explanation that I offered.

Your explanation is correct in every way.

The confusion might actually lie with me based on your last question.

ShowType (in my head) is a way to allow me to sort out the different categories of show.

For example, the MAJOR show that is going on is called the Legends Night. This show features some of the most successful American songwriters alive today. It's one night only and will host around 10-15 artists.

The MAIN grouping of shows however will be the club shows. There will be over 100 of these. So, I would like to be able to use the same database for these with different programming to pull out JUST the Legends show or JUST the club shows.

[creole]

I just got finished reading most of that link you gave me Shane. It's very good, I printed it out. Makes me wonder if I shouldn't invest some time and cash and take a course in proper db design and theory.

[shane]

I think there is a need for the ShowTypes but I think the list of possible ShowTypes you listed previously may not be quite right.

Legends Night - a one night show
Piano Night 1 - a one night show
Piano Night 2 - a one night show
Open Mike Night - a one night show
Kid's Shows - a one night show
TV Taping - a one night show
Club Shows - These shows will make up the bulk of the DB as there are 4*20*5 artists performing over the 5 days.

There's no obvious distinction between Piano Night 1 and 2. I think just having a Piano Night ShowType would make more sense and the difference would come about from the 2 different Shows.

There may well be a clear distinction between the 2 ShowTypes (eg Jazz piano night and Classical piano night) and if there is I think the ShowType name needs to be clearer.

I've attached an ER diagram, it may help in understanding the relationships between the Entity's. (Note that the foreign keys are not shown in the Entity attribute lists as these are implied by the relationships).