Database structure and potential queries

[creole]

Hey guys...

I'm building a website for an event called Tin Pan South. It's a week long series of concert focused on Sing-Songwriters in Nashville.

Because there will be so many show, artists and venues and they might change at the last minute, I'd like to use a database. The three main areas of the DB (and their fields) will be:

Artists
- ID
- FirstName
- LastName
- Bio
- Photo

Venues
- ID
- Name
- Address
- Phone
- MapImage
- Details

Shows
- ID
- Date
- Time
- AID (artist id)
- VID (venue id


The page that lists the show will allow users to sort by Artist, venue or date like so:

Date
Artist   Venue   Time
Artist   Venue   Time

Artist
Date   Venue   Time
Date   Venue   Time

Venue
Date   Artist   Time
Date   Artist   Time


Now, I have questions?

1) Is this a good structure for this DB?
2) How would I query the DB to perform the above sorting?

This site will be built in Cold Fusion. I don't think I should have any problems with the coding, but the querying part always messes me up. I have a hard time when working with fields that relate.

Help me out please?

[Technosailor]

well..I was gonna help you out 'til you said Cold Fusion...now you're on your own!

Sketch

[MattR]

Looks OK to me.

A sample query could be something like:

SELECT ..
  FROM Artists a, 
       Venues  v,
       Shows   s
 WHERE s.AID = a.ID
   AND s.VID = v.ID
 GROUP BY -- insert whatever method you wish to group by (e.g. all artist's shows together would be GROUP BY s.AID)
 ORDER BY date, time -- I suspect it would be something like that

[creole]

Originally posted by MattR
A sample query could be something like:

SELECT ..
  FROM Artists a, 
       Venues  v,
       Shows   s
 WHERE s.AID = a.ID
   AND s.VID = v.ID
 GROUP BY -- insert whatever method you wish to group by (e.g. all artist's shows together would be GROUP BY s.AID)
 ORDER BY date, time -- I suspect it would be something like that

Matt...first off, thanks for posting. As soon as I saw your name, I knew I was going to get a good answer. Would you mind elaborating on one part please though?

FROM Artists a,
Venues v,
Shows s

Are you simply using "a" as a placeholder for an actual field I might use? The reason why I asked for help in this is that I make queries like this, I tend to get every field in the database printed out for every listing I get back. I know it's something that I do wrong, but I want to understand what's going wrong so that I can avoid it in the future.

Thanks again Sir!

[dhtmlgod]

Ugh! No offence creole, but the schema is bad!

I would suggest reading up on normalisation and atleast take it to the 1st normal form as its your first time!

This is how I would structure the db:

Artist
- ID
- firstName
- lastName

ArtistInfo
- ID
- artistID
- bio
- photo

Venues
- ID
- details

VenuesContact
- ID
- venueID
- address1
- address2
- address3
- city
- state
- zipCode
- phone
- fax
- eMail
- siteAddress
- mapImage

Shows
- ID
- time
- date

showInfo
- ID
- showID
- artistID
- venueID



If it is going to take a big toll on performance, you can denormalize it (pm me if ya want)

What database software will you be using? If your going to use access, I can make it for you and eMail it for Monday.

But... you don't have to listen to me... I'm no database expert

[Technosailor]

dhtml,

I'm not sure I understand what your reasoning is behind having the Artists and the Artist table. Seems all the information in Artist is in Artists...Am I missing something?

Sketch

[dhtmlgod]

Whoops, fixed it.

Thanks sketch

[creole]

I know I'm not even a novice at DB design. That's why I posted it here. I just talked to the lady that's running the event and she gave me some information that let's me think that I need to redo the DB anyway. I thought that there would only be around 100 artists. Turns out that there's going to be almost 500. Only a handful of those are anyone of note so they won't have information on the rest.

Instead of billing a show as "Joe Normal at the Bluebird Cafe" it's going to be "Songwriters Night at the Bluebird, featuring Joe, Jim, Mandy and Tito"

Do you see the difference? Because each show features 4-5 performers, with 20 shows a night for 5 nights, they just don't have the resources to get information for each of the performers. I'm meeting with them today to get more information. After I've met with them, I'll have a better understanding of how the DB should be set up. I'll get back to you on this one later today or tomorrow.

[MattR]

Originally posted by creole

Matt...first off, thanks for posting. As soon as I saw your name, I knew I was going to get a good answer. Would you mind elaborating on one part please though?

LOL, no problem. Glad I could help!

Originally posted by creole

FROM Artists a,
Venues v,
Shows s

Are you simply using "a" as a placeholder for an actual field I might use? The reason why I asked for help in this is that I make queries like this, I tend to get every field in the database printed out for every listing I get back. I know it's something that I do wrong, but I want to understand what's going wrong so that I can avoid it in the future.

Thanks again Sir!

Correct, it is a form of shorthand. Actually, it is a 'shortcut' to writing it as:
FROM Artists AS a, Venues AS v, etc.

Basically it allows you to reference table columns like this:
a.some_column rather than aVeryLongTableName.some_column

[dhtmlgod]

Creole, when you get the final info, I can design the database for you, and if your going to use Access, I can build it and the queries for you.

If your going to use some thing else, I can still design it, and code some rough queries that would possibly only need a little editing to work on another DB.

[MattR]

Originally posted by dhtmlgod
Ugh! No offence creole, but the schema is bad!

I wouldn't say it was bad. It fit the requirements as stated and didn't have too much weird info.

There are bad things in yours as well,

I would suggest reading up on normalisation and atleast take it to the 1st normal form as its your first time!

This is how I would structure the db:

Artist
- ID
- firstName
- lastName

ArtistInfo
- ID
- artistID
- bio
- photo

I don't see the need to have two tables here. ID is the identifier for an Artist; there's no multi-dependancies in the artist table as is (I'd say it would be in the 3NF!).

P.S.
You should NOT have an ID (I assume auto_increment?) on the ArtistInfo table. ArtistID is a perfect primary key (I doubt you'd have multiple bios/photos for an artist).

Venues
- ID
- details

VenuesContact
- ID
- venueID
- address1
- address2
- address3
- city
- state
- zipCode
- phone
- fax
- eMail
- siteAddress
- mapImage

Maybe YOU should read up on 3NF. You have a multivalued dependancy here. CITY can only live in a single state or zipcode.

If you want to really get picky about normal forms, you'd have to do something like:
VenuesContact
-- get rid of the ID column already!
venueID
..
cityname (or whatever, maybe city ID since you can have many cities in a country with the same name )

City
Name

ZipCode
ZipCode
State

-- I think this would make the most sense..
-- It depends on if zip codes can extend to other
-- states, if so then zipcode would need state removed
-- and then another many-to-many table created. Would
-- need to research that further though.
CityZip
City
ZipCode

State
Name
Country -- you can have venues in different contries when bands tour, you know

Country
Name

Shows
- ID
- time
- date

showInfo
- ID
- showID
- artistID
- venueID

Again, drop the ID col on showInfo. I disagree with showInfo having venueID; I assume a particular show can only happen in ONE venue (e.g. Dave Matthew's Band can only be at one place in a particular moment in time! ) so I'd move venueID to the shows table. You have reptition of data if you have more than one artist in a show (since your venueID would be repeated).

If it is going to take a big toll on performance, you can denormalize it (pm me if ya want)

What database software will you be using? If your going to use access, I can make it for you and eMail it for Monday.

But... you don't have to listen to me... I'm no database expert
[/B]

Nope, but you've got a very good start. Keep in mind that if you have perfectly good keys (e.g. ArtistInfo.artistID) it is very redundant AND A BAD THING to add an auto_increment integer to a table like that. Other than that, and a few other normilization things, you're doing well.

However, I think the original DB fits the bill of being easy to use (all the data is where you need it) and not overly complex. Sure, we could add city, state, zip, relationship tables, etc. -- but there's no need at this point (unless maybe you'd want to show all shows in a particular zip code or city or whatnot) and having an 'address' col is simple enough for most applications (I assume this is a small application which is filling a very specific purpose).

[dhtmlgod]

*Goes and sits in the corner*

[MattR]

Originally posted by dhtmlgod
*Goes and sits in the corner*

I apologize if I came across as disparaging.. We need more good RDBMS developers so don't give up!

[creole]

Don't mind him Matt...he's our whipping boy.

I wanted to point out that all of the shows will take place in Nashville, TN, which is why I didn't list a City, State or Zip.

[DaveMaxwell]

I was going to reply w/ quote to MattR's next-to-last post, but the reply with quote would only give me the last part of his post , so I'll try to cut and paste as needed and hope I don't miss anything....

I would agree with MOST of what you said MattR, but I can't agree with ALL.

Artist table:

You should NOT have an ID (I assume auto_increment?) on the ArtistInfo table. ArtistID is a perfect primary key (I doubt you'd have multiple bios/photos for an artist).

What about a band? If you decide to show a bio of every member of a band, you would need the autoincrement/ID field to allow for uniqueness (unless you allow duplicates (a policy I abhor as much as possible). You MIGHT want to have a band table instead, I don't know. Should be something to take into consideration.

Maybe YOU should read up on 3NF. You have a multivalued dependancy here. CITY can only live in a single state or zipcode.

A city can cover multiple zipcodes and in some cases, can cover multiple states. HOWEVER, in this case I would agree with D because a venue would only have one city/state/zip to go with the contact information.

Again, drop the ID col on showInfo. I disagree with showInfo having venueID; I assume a particular show can only happen in ONE venue (e.g. Dave Matthew's Band can only be at one place in a particular moment in time! ) so I'd move venueID to the shows table. You have reptition of data if you have more than one artist in a show (since your venueID would be repeated).

Depends on the definition of venue. There is a concert series that happens around here that has 3-4 stages going on at one time. Does venue mean the ENTIRE area, or one stage at an area. That would need to be defined further before finding fault one way or the other. If a venue is one whole area, then you are correct, otherwise I don't think either approach will fully meet the needs and a new table will need added perhaps...

[creole]

OK guys...let me update everyone. I met with the client yesterday and I have a better understanding of what's going on. First off, let me post this screencap of a potential DB structrure.



Now, let me explain in more detail.

As you can see, I've added a TABLE (ShowType) and renamed one TABLE (Shows is now Performances). I found out that instead of there being one artist for a particular show, in a particular time slot, there will instead be multiple artists in one show, in one time slot.

A possible display for one event might look like this:

VENUE
DATE   TIME   01,02,03,04
Where the numbers correspond to a particular artist.

Let me take a second to note that this DB will likely live on to next year as this is an annual events that has been going on for quite a few years.

Now, I think the relationships above make sense to me. I don't believe that I'm duplicating any info and everything seems to be modularized. My questions are:
a) Am I right in my statement?
b) How would I query the DB to gather the proper information?
c) Is it possible to put multiple artistIDs (AID) into the performances.AID? If so, how would I do that?
d) Am I getting too far over my head? Be honest.

[DaveMaxwell]

I don't think you're necessarily getting in over your head, but the database design won't quite work the way you want it to. The looping dependencies is a bad idea. Here is where I think you should take this (purely my ideas, let the others rip it apart)

Artist (still only individual artists, no bands, correct?)
- 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

ArtistType
- ArtistID (FK to ArtistID on Artist)
- ShowTypeID (FK to TypeID on ShowTypeID on ShowType)

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

Performance
- PerformanceID (PK)
- Date
- Time
- 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)

After Matt and D get done ripping up this dbase design....err we can agree on the best design, we'll work out the coding. One step at a time...

[creole]

PK = Primary Key
FK = Foreign Key

right? I've never worked with a lookup talbe before so I'm getting into somewhat unfamiliar territory. But that's good. You can't learn if you never stretch yourself right?

I want to take a minute to thank you guys, Dave, dHTML and Matt. I really appreciate your help. Hopefully some of your knowledge will rub off on me and I won't have to ask this question next time.

One thing though. Does Access allow you to "define" a Foreign Key? Or is an FK implied by the lookup table?

[creole]

BTW...

I do want to make sure this DB is well designed as it will be carrying on, possibly past my employment here. So I would rather stretch myself and have the DB done right than try to make it easy on myself.

[MattR]

Originally posted by DaveMaxwell
[B]I was going to reply w/ quote to MattR's next-to-last post, but the reply with quote would only give me the last part of his post , so I'll try to cut and paste as needed and hope I don't miss anything....

ARGH! It did the same thing to me! dsfjsdlj

Artist table:
I was assuming the Artist table was an individual person (e.g. Dave Matthews, Alainis Morisett, etc.). You're right, if we want band bios we'd need a different table structure:
Band:
BandID
Title

Artist:
ArtistID
BandID -- (assuming a particular band member can only be in a single band; otherwise a m-to-m table)
Bio

City Stuff
That's true, a venue probably would follow that, although D was talking about normilization and how it should be read up upon. I was making the point that you can take normilization any number of ways but from a *strict* sense (not that I would advocate doing 3 tables for location info in this case, but others I could see where it would make sense) you should normalize some more.

Venue:
I was assuming a venue would be the 'entire area' so to speak. I'm not sure how you'd want to handle a Main Stage vs. Secondary Stage -- you'd have to create two venues to accomodate that? I guess you'd need to have maybe:

venues:
VenueID
..etc..

stage:
StageID pk
VenueID
.. etc ..

Then, Performance:
PerformanceID
StageID
.. etc. ...

It all depends on how complex the requirements are (Creole maybe if you could explain where the concert will be etc. we can come up with a DB which fits this situation. From what you've said, this is a DB just for this particular concert so we can assume some things which would break if it was a more general DB, correct?)

[MattR]

Looks good, Dave. I have a question though;

What lives in ShowType? I'm assuming something like "Bluegrass", "Rock", etc.

Is there a need for a many-to-many table for Artists and ShowType? I'm not familliar with the type of music creole is writing this application for so I'm not sure if these bands would be classified as "Rock" and "Bluegrass" at the same time, but I know when I go into CompUSA and look at CDs/DVDs I only find a particular band in a single type. I assume that is
1) for consistency / that is what the band wants and
2) it's cheaper to keep CDs in one place than place twice as many in two locations.

I see the ShowType also shows up in a performance -- is that saying the band is playing a particular style at this particular performance? I'm confused!

Originally posted by DaveMaxwell
ShowType
- ShowTypeID (PK)
- TypeDescription

ArtistType
- ArtistID (FK to ArtistID on Artist)
- ShowTypeID (FK to TypeID on ShowTypeID on ShowType)

Performance
- PerformanceID (PK)
- Date
- Time
- VenueID (FK to VenueID on Venue)
- ShowTypeID (FK to ShowTypeID on ShowType)

[creole]

Your questions answered:

- ShowType will be a "category" of shows. There are seven main categories as follows:

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.

So, I label the each show so that I can sort them out on the page.

ArtistsTable will only have single artists. There are no "bands" performing as this festival focuses on the individual singer-songwriter.

Each venue will be a unique location. One stage per venue.

Any other questions

[Technosailor]

WOW! What a phenomenal thread....MEeting of the minds here.

Sketch

[creole]

Meeting sof the minds minus me and you you mean...



If I was in the Multimedia forum I would feel secure. Since I'm in the Database forum, I feel small. I'm just excited to be learning from these guys. I've respected Matt's input ever since I read his exhaustive post on databases about almost a year ago.

[Technosailor]

lol...me? I'm not in the meeting period. I'm like the guy who sits in the halls of Congress...in the balcony watching all the proceedings....

Sketch