SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 63
-
Feb 13, 2002, 17:37 #1
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Database structure and potential queries
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?Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 13, 2002, 17:39 #2
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
well..I was gonna help you out 'til you said Cold Fusion...now you're on your own!
Sketch
-
Feb 14, 2002, 02:10 #3
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Looks OK to me.
A sample query could be something like:
Code: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 - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Feb 14, 2002, 10:29 #4
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by MattR
A sample query could be something like:
Code: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
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!Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 14, 2002, 11:05 #5
- Join Date
- Jul 2001
- Location
- Scotland
- Posts
- 4,836
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Last edited by dhtmlgod; Feb 14, 2002 at 11:16.
-
Feb 14, 2002, 11:12 #6
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Feb 14, 2002, 11:15 #7
- Join Date
- Jul 2001
- Location
- Scotland
- Posts
- 4,836
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Whoops, fixed it.
Thanks sketch
-
Feb 14, 2002, 11:34 #8
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 14, 2002, 11:44 #9
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
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!
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_columnMatt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Feb 14, 2002, 11:47 #10
- Join Date
- Jul 2001
- Location
- Scotland
- Posts
- 4,836
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Feb 14, 2002, 11:56 #11
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by dhtmlgod
Ugh! No offence creole, but the schema is bad!
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
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
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) 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]
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).Last edited by MattR; Feb 14, 2002 at 11:58.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Feb 14, 2002, 11:58 #12
- Join Date
- Jul 2001
- Location
- Scotland
- Posts
- 4,836
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
*Goes and sits in the corner*
-
Feb 14, 2002, 12:23 #13
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by dhtmlgod
*Goes and sits in the corner*We need more good RDBMS developers so don't give up!
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Feb 14, 2002, 13:30 #14
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 15, 2002, 08:38 #15
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
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).
Maybe YOU should read up on 3NF. You have a multivalued dependancy here. CITY can only live in a single state or zipcode.
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).Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 15, 2002, 11:48 #16
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 15, 2002, 12:10 #17
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
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...Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Feb 15, 2002, 12:14 #18
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 15, 2002, 12:16 #19
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 15, 2002, 12:20 #20
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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....
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?)Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Feb 15, 2002, 12:31 #21
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Feb 15, 2002, 12:36 #22
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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 questionsAdobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 15, 2002, 12:46 #23
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
WOW! What a phenomenal thread....MEeting of the minds here.
Sketch
-
Feb 15, 2002, 12:59 #24
- Join Date
- Oct 2000
- Location
- Nashvegas Baby!
- Posts
- 7,845
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Adobe Certified Coldfusion MX 7 Developer
Adobe Certified Advanced Coldfusion MX Developer
My Blog (new) | My Family | My Freelance | My Recipes
-
Feb 15, 2002, 13:13 #25
- Join Date
- Jun 2001
- Location
- Before These Crowded Streets
- Posts
- 9,446
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Bookmarks