Sketching a Relational Database: seeks Best Practices

ATS16805

I wish to build an event calendar for a local live music venue. i actually tried this once back in my other life as a DreamWeaver / Coldfusion user, when i knew nothing of the true RDB. i basically made a flat-file on steroids (MS Access db) w/ a lot of columns. if you care to view it, it is still on-line here. (that url is only useful if you want to reference it after reading the paragraphs below-- so you can see what data i want to enter into my db). the basic idea behind what i want to build is this. please bear w/ me as this is the only way i know how to explain things-- by telling a story.

this venue showcases the region's non-stadium touring, national rock concerts. i was publicist for this venue, and during my tenure there, i came up w/ the idea to create a database driven calendar system. this would accomplish a few things to the ultimate end of boosting productivity, efficiency, and accurate reporting of concert news to both the public and the news media.
having witnessed the venue's singular manager labor at hand-coding so many <TR><TD>info</TD></TR>'s that it was enough to hurt my eyes, not to mention the time consumed. the info he was publishing included public event info to be posted on their website. this, of course, was a duplicate of info which i would communicate to the local news media, and our street team, various online entertainment listings, etc. However, not all info that i distributed would include the same amount of content. this is the important part-- so please focus-- i know i'm probably losing you. 😉
i had to ensure that the news media received either photographs and press release, etc from the artist, or i had to send them the appropriate URL's so that the could retrieve the info for their writers. in 99% of the cases, the artist tour manager would provide a URL-- it seems as though this is the modern way of doing things in the music biz-- that is to have a URL from which a PDF, or HighRes photo can be downloaded for Newspaper publishing, and there is typically a press release, bio, etc there. so, i had access to the following CONFIDENTIAL info: tour manager name, phone, e-mail, and the aforementioned URL, and often an attached photo or Mp3-- any schwag that the artist publicist would tag onto this tour promotion. often i got the info, as might imagine, from different sources for the same artisit (tour manager would give me publicist, would give me URL, would give me Management Agency, etc). It was part of my good performance to complie this data for the news media.
bear in mind that within all of that compiled info was of course the public info as well-- the tour date, tix prices, etc, and then i had to know venue drink specials, and other venue specific info so that our street team could distribute our own published hand-bills to promote the venue/ show.
My PROPOSED SOLUTION:
create a database which would hold all of this info. allow the venue manage to input the data (as he is the first to receive all tour manager, artisit publicist, correspondence from the artist/ tour) into an html form which would store the info in a database. there would be two output pages. one public, and one confidential for only the news media's viewing.
now, like i said, i was on the track to building this app. i got pretty far w/ it for my little knowledge at the time. but now i realize that this might best be done by implementing a RELATIONAL database so that certain items could be linked by a relational id key-- for example, the artisit could have a single id, and it could be referenced upon his or her next tour. for regular venue nights, such as a "Nickle drink night" in which there is a local act at the bar, there could be an ID key for the days.

do you see where i'm going w/ this? i have never built a relational database before. geez. i feel silly that i had to tell that entire story to get to my issue at hand. what i would like to know is this-- do you have any resources for rules-of-thumb in building a relational database-- or perhaps better yet-- are there any RDB wizards out there? something "automatic" into which i might enter bits of data and let it decide what would be the most efficient relational database model? heck, if anyone cares to help me sketch the tables and keys, i would be most grateful, but i realize that's a lot to ask.

thanks for your patience. i hope i didnt bore you too much or waste too much of your time. i look forward to hearing what you have to say as this is a project which i've been trying to complete successfully for quite some time now. thank you so much.

EDIT:
i don't know if it matters in terms of DB design, but in the end, i need to be able to provide absolute URL's in publicity mailings so that fans and media may link directly to a single concert date w/out having to filter through an entire month of dates. however, there should be an option for month view as well.

bastien

google for tutorials on relational design, there are tons like:
http://www.geekgirls.com/menu_databases.htm

Its not that hard to build a simple data base, of course this is said based on years of experience. Once you learn the basics of relational design, you'll find it makes logical sense.

As for rules of thumb, group like objects together; e.g. you have bands, venues, tour managers(contacts). Each of these, to me, is what I'll call a helper table to keep things simple. That is it contains data that is widely used, but not frequently updated. So the info may change from time to time, but not regularly.

All these tables would feed a main tour table that is the calendar. It would contain a unique id, date, band_id, tour manager_id, venue_id and other items related to that date as you see fit. This table is the work horse of the application. It is the most frequently changed table, hopefully mostly in the case of additions to the calendar.

Most of your queries will tie together the diff tables to provide the user with coherent info, so they see the band name instead of the band id etc, in a single web page...

This type of data is very suitable for a content management system(CMS). You may want to look at something like www.mamboserver.com and their events module to see if this can work for you...

coreyp_1

I'm working to switch over to the Drupal CMS. It, too, would be worth checking out.

ATS16805

Bastien,

my profuse, codrial thanks to you for providing such a thoughtful, informative, and quite helpful reply. I appreciate that you took the time to think of the true application of this RDB, and to go so far as to present to me a proposed solution to my problem by sketching the model (i guess, that's the jargon "the model"?)

in the meantime from when i posted my original novella, i got to it in the way that works for me-- by opening Excel, making columns, and grids for tables, and just trying to get a look at it, so i might start putting things together. it's funny-- the book i'm reading, sams teach yourself php... (EDIT: i should say "book i'm referencing, as i've read it a long time ago, and i just keep hovering over these examples, trying to develop what mastery i can w/ what i have-- it really has helped a LOT. i've made a ton of notes, which i keep in separate folders w/ the respective "example projects"... that's a tip in disguise, for you fellow novices, if you didn't catch it... but i digress...) suggests drawing it out on paper, and even using "post-it" notes if necessary-- for me, to use Excel is a very logical method. i wonder if it is not a good technique for sketching? i don't recall ever hearing of people doing it this way, but for some reason, the monitor is just as good as a tablet and pencil for me-- at least when i can emulate the shapes consistently, and neatly in this manner. blah, blah..

anyway, here's what i came up with. my screen res is over 1200 wide, and this is Excel maximized at i think 80%. so it's a wide-load, but i'm sure you'll manage. 🙂 presuming, you have a similar res going on. firefox "magnifing glass" thing making it a cinch, of course.

as you can see, i had originally called my weeks the master table, however, this is not how it trickled down into MySQL. i made the "Concert Table" the master table. and put Artist_ID1, Artist_id2, artist_id3 inserted after concert_id. it's a lot to chew on for my first try. this jpg may also reflect a version prior to my realization about proper Syntax for a couple of those red rows (the MySQL data types).

a true novice, here's where i am currently:
having never done anything like this before, only in examples in the Sams book, i decided that the closest model therein was an "online address book" example, in which i cut and pasted my way to an entry form for-- get this-- the ARTIST TABLE. i don't know why i started there. but, it's a chewy morsel, that's for sure (for, the novice).

although this is not an EXACT replica of the MySQL database which i have completed w/ the tables as shown here, w/ the ONLY addition being a text field in each table for comments, and the artist_id* columns in the concert table (so i can tell it who is playing on that concert date), this should give you a good idea of my thought process. like i said, i started w/ the days of week being the master, and through the first, second, and third normalizations (jargon?) levels, i then realized that something was amiss, and changed that aspect of the schema.

my real problem-- the reason i haven't moved on it since i started hacking up that address book, is that i'm having difficulty getting around the whole "what do i do now" step. which form should i build first, and what needs to do what, and interact w/ which other table elements at which critical stages, etc. i'm just blown away right now.

i'd really appreciate some help getting through this step. thanks!!!

edit2:
guess i really AM burned out on it. i didn't even see that this jpg does reflect my current tables. i forgot i made the updates before i closed it last night.

coreyp_1

The reason I use Drupal is because it already has a user management system, and is very open ended when it comes to customization, which is probably the direction you will be heading.

As for what to do next:
My philosophy is that you can't display anything unless you have data to display, so you should probably write your input forms first. To determine which one to do first, examine the dependencies and work in reverse.

You cannot add data to your concert table until you have artist info. You cannot have artist info until you have contact info. Therefore, you should probably build your contact input form first. This way, when you build your artist form, you can have a drop down box to select the appropriate contact info. Likewise on the Concert info.

Suggested Changes:
First, I would not limit the concert to just 3 artistid, but make a separate table of 2 fields, one with the concert id, and the other with an artist id. This way, a concert can have anywhere from 0 to 1,000,000+ artists at a single concert with no modification to the code: just loop through the data!

On second thought, this middle table should have 3 fields: one with the concert id, one with an artist id, and one to identify that record as the featured artist at the concert. Again, this way you can identify 1 (or any number) artist as the featured artist for that event, to distinguish them from the rest.

Second, I do not see how the Master table links with the rest of the information (perhaps by the Concert table?). At any rate, I do not see a need to have a drink_id as well as drink, beer_id as well as beer, etc., unless it is serving some purpose I am not seeing at this moment.

It seems as though perhaps you should have a bar table, with the bar's name and other info. Then have a second table with the nightly drink information and specials, if any. The order of dependencies works here, too: You can't enter drink information until you have a bar to associate it with. This would be an interesting approach, because then you could have more than one (or none at all) drink specials a night.

General Flow For Creating A System:

Determine what database structure is needed. (You're working on that.)
Use dependencies to determine which forms need to be created first. Often, the smallest parts (like the contact info) will hold up production, unless tackled first.
Define a system to handle the data, then be consistent!

Here is the system that works for me. It uses one page to handle adding new information, editing old information, error checking, etc. It's creation flows something like this:

Create the input form, which posts information back to itself.
When data is submitted, check for errors. If errors are found, show an error message and re-print the form.
If no errors are found, perform the necessary action, and do not print the form.
Create a new page that outputs a table of the new information (ex. display all of the contacts in the database, on a single page.)
Create a way to edit the existing information. As I stated above, I use the same page as the edit page, (which will already has the error checking provided). This was difficult to do my first time, but now has become quite easy (and fast!) to implement.
Provide a way to delete the information (if needed).
Move on to the next element.

It appears that I, too, have written a small novel. I hope the information makes sense... It's getting late!

ATS16805

coreyp_1 wrote:
Suggested Changes:
Second, I do not see how the Master table links with the rest of the information (perhaps by the Concert table?). At any rate, I do not see a need to have a drink_id as well as drink, beer_id as well as beer, etc., unless it is serving some purpose I am not seeing at this moment.

It seems as though perhaps you should have a bar table, with the bar's name and other info. Then have a second table with the nightly drink information and specials, if any. The order of dependencies works here, too: You can't enter drink information until you have a bar to associate it with. This would be an interesting approach, because then you could have more than one (or none at all) drink specials a night.

Thanks VERY much for your help. i believe w/ both of the extended replies here, i will be able to move forward this beast! i do have a couple more questions 😉

also, i updated the jpg, so it's closer to my real db now

to clarify, because i'm getting the impression that i didn't explain it quite right, and the respondants didn't pick up on it this important quality of the app-- this is a Concert Event Scheduler for one venue only. so, maybe that will make the drink_id seem more clear, and also reveal why i have made what is called my "Days of Week Table", thereby allowing me to use a "day_id" to associate the drink specials for that particular day-- in this particular bar, they have the same specials rotating weekly, so i really only need 6 drink id's (they're not open on sundays), but i left it open so that when/ if they might change a drink special, they ability to do so is wrtten into the app.
Edit: hmm. i'm starting to think that perhaps i haven't done my "Days of Week" table most efficiently. the idea behind it (shown in jpg image as "master table") is to allow for day_id#1 to input all info for that day into the output-- thereby showing drinks, pitchers, and beer features all at once. i left those features open for adding new ones. did i need to do it that way, and is this going to work w/ the rest of my layout? sorry-- it's still difficult for me to get my head around it.

my plan, and i don't know if this is the best approach for the ultimate functionality of the application, but for keeping it consistent w/ the existing layout of their site, i want my "results" page to look like this. does it make sense as to why i want/ need a drink_id after viewing that page?

hey, thanks again! it really is going to help me. i'm excited to work on it again. darn daily e-mail delaying me! hmph!

ATS16805

hello. i wanted to be sure that you were aware of my edited revisions in my post above-- in case you might have any different advice based on the fact that this app is being designed for the in-house use of a single concert venue.

tonight is the first i'm getting to work on this since we talked about the relational table structure. i'm about to commence building my input forms based on "dependencies", using the method as described by coreyp_1.

coreyp_1, if you're out there (or anyone reading), do you still think it's best that i create that extra table for contact_id and artist_id? typically, the shows never have more than 3 acts, but i guess i should plan for any possible lineup. if i do add that table, how do i need then to modify my existing tables?

in the meantime, here goes...
🙂