|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
April 8th, 2005, 12:05 PM
|
|||
|
|||
|
Database structure - music reviews website
Hi,
I have a music reviews website. It takes up quite a bit of time, what with organising and reviewing concerts and any CDs that land on the doorstep. The site is currently updated by the slow and time consuming method of pasting text (and sometimes images) from reviewers' emails into an html page, which I then save. A link to the new page is then added to three other pages (main page, what's new, archive) before the whole lot is uploaded to the web server. I really want to get away from this slow, error-prone method and move towards a mySQL database which holds all of the information necessary, and also enables me to update the site a lot more easily. I have everything I need to do this (lycos package), except for the knowledge. We publish articles on: *Albums *Singles *DVDs/Videos *Live shows *Demos *Interviews *Previews of upcoming events Obviously, each of the above types of article will need to have its own table. The things that bother me most are: *How to normalise the data (bridge tables?) *How to create relationships between tables (artist - album etc.) *How to update several tables at once (i.e. a new review of an album for a new artist that doesn't already exist in the artist table) All of my tables are in myISAM format. Will this prevent me from doing any of the above? The only other options I am given are: *Heap *Merge *ISAM Sorry about the length of this post, but if you get bored reading it, you can always have a look here (and then reply)! I hope someone can help with this. Andy
|
|
#2
April 8th, 2005, 03:38 PM
|
|||
|
|||
|
At this stage, I'd say the best advice I can give is to not skimp on the db modelling/normalisation/schema setup. It can be confusing and frustrating, but the tighter you have that part locked down before you write your first line of code, the better off you'll be. There are a number of good normalisation/modelling tutorials out there (a google and/or igrep should turn over more than enough). Read a couple of those and start writing down dummy tables.
More specifically, a separate table for each article type may not be necessary -- a simple article_type field in your main articles table may suffice, depending on your particulars. Off the top of my head, I'd start with something like: ARTICLES article_id, artist_id, title, body, type ARTISTS artist_id, name, details Assuming each article is about 1 artist; otherwise, I'd remove artist_id from ARTICLES and set up another table: ARTIST_ARTICLE article_id, artist_id
|
|
#4
April 13th, 2005, 09:17 AM
|
||||
|
||||
|
I was going contest your structure Madpawn, until I read the last line... =)
I was going to state that not all articles might be about one artist... A simple example of how nailing down the design will help things in the future. Had something like this not been thought of prior to plans moving forwards, you would have a hard time rolling back and redesigning. stubbsini, mind if i ask the site address? (somewhat unrelated to actual thread)
|
|
#5
April 21st, 2005, 02:17 PM
|
|||
|
|||
|
Quote:
Sure...it's www.thisisnottv.co.uk As you'll see, it's basic and not great for navigation. It's something I took over in the New Year in order to save it from extinction when the people who ran it before me said they were quitting. I had the idea to revamp it using DW, but then someone suggested DBing the whole thing. Basically, I want to be able to 'drop' review data into it with minimum hassle. stubbsini
|
|
#6
April 22nd, 2005, 10:08 AM
|
||||
|
||||
|
if DW means dreamweaver, I urge you to re-evaluate your decision.
Might be a good time to learn WS (Web Standards)... Not too many designers are using them, and it will definately give your page a cutting edge appeal. I definately think your site is perfect for a DB design. Looks like your page is all static now; it must be a hassel to update.
|
|
#7
April 22nd, 2005, 11:21 AM
|
|||||
|
|||||
|
Quote:
It certainly does. The main justification for choosing DW was that I could afford to buy an official version. I'd used Frontpage before and hated it compared to DW. Keeping the cost down is key, as I don't get any money for running the site. Quote:
Never heard of 'Web Standards' - does it/do they have a link? I'll Google it... Quote:
It's the main pain in my ass! I get reviews in by email; some in Word, some in the email body itself. Then I copy and paste them into a template (of sorts) in DW, before saving the file and then manually adding a link to three different pages before finally FTPing them to the server. DBing is definitely the way forward, it's just the time it will take to do it.
|
|
#8
April 22nd, 2005, 11:51 AM
|
|||
|
|||
|
I think the point MadCowDzz is trying to make about Dreamweaver is that it generates inefficient, bloated, and unnecessarily confusing, which can make later editing difficult.
DW's got some nice tools, and it's far from a bad program, but I wouldn't rely on it to create your code for you. You'll be much better off coding it yourself, and coding it to standards. But before you do that, get your db worked out. 
__________________
"A pawn is the most important piece on the chessboard -- to a pawn"
|
|
#11
April 22nd, 2005, 01:16 PM
|
||||
|
||||
|
i'm a pretty big advocate of the do-it-yourself design ideas.
I believe in a simple text editor for site designs... dreamweaver certainly does help in rapid development, and if you're used to it you will likely find it easier todevelop sites that way than with notepad. Personally I feel like i lose a bit of control with a WYSIWYG editor... I guess it's a matter of personal preference and level of knowledge. The important part of a new website is the development phase, such as DB development and GUI design. Make sure you nail that down before you worry about a tool... I probably shouldn't have brought out my DW prejudice so early in our relationship =) Web Standards are a popular style of designing webpages. It helps design code that is viewable on all browsers (IE, Netscape, PDA browsers, disabled users [such as blind and visually impaired)... There's a wealth of resources on the net... but again, need not worry about that yet. [Read the Web Standards FAQ for some ideas] I've really driven this thread off-topic... =)
|
|
#12
April 27th, 2005, 10:09 AM
|
|||
|
|||
|
Quote:
Hi guys, Been away from this for a while as I've been doing a few reviews...I saw a band called Idlewild on Saturday - check out the review at www.thisisnottv.co.uk (shameless plug!)... Anyway, this is what I created: # -------------------------------------------------------- # Table structure for table `article` # CREATE TABLE article ( id smallint(5) unsigned NOT NULL auto_increment, artist_id smallint(5) unsigned NOT NULL default '0', title varchar(64) NOT NULL default '', label varchar(32) NOT NULL default '', reviewer_id varchar(24) NOT NULL default '', text blob NOT NULL, create_date timestamp(14) NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM COMMENT='Album table'; # -------------------------------------------------------- # # Table structure for table `article_photographer` # CREATE TABLE article_photographer ( article_id tinyint(4) NOT NULL default '0', photographer_id tinyint(4) NOT NULL default '0', create_date tinyint(4) NOT NULL default '0', PRIMARY KEY (article_id,photographer_id) ) TYPE=MyISAM; # -------------------------------------------------------- # # Table structure for table `article_reviewer` # CREATE TABLE article_reviewer ( article_id tinyint(4) NOT NULL default '0', reviewer_id tinyint(4) NOT NULL default '0', create_date timestamp(14) NOT NULL, PRIMARY KEY (article_id,reviewer_id) ) TYPE=MyISAM; # -------------------------------------------------------- # # Table structure for table `artist` # CREATE TABLE artist ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(48) NOT NULL default '', create_date timestamp(14) NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM COMMENT='Artist table'; # -------------------------------------------------------- # # Table structure for table `artist_article` # CREATE TABLE artist_article ( artist_id tinyint(4) NOT NULL default '0', article_id tinyint(4) NOT NULL default '0', create_date timestamp(14) NOT NULL, PRIMARY KEY (artist_id,article_id) ) TYPE=MyISAM; # -------------------------------------------------------- # # Table structure for table `photographer` # CREATE TABLE photographer ( id tinyint(4) NOT NULL default '0', name varchar(36) NOT NULL default '', email varchar(48) default NULL, photo varchar(64) default NULL, create_date timestamp(14) NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM; # -------------------------------------------------------- # # Table structure for table `reviewer` # CREATE TABLE reviewer ( id tinyint(3) unsigned NOT NULL auto_increment, name varchar(32) NOT NULL default '', email varchar(48) default NULL, photo varchar(64) default NULL, create_date timestamp(14) NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM COMMENT='Reviewer table'; # -------------------------------------------------------- Note that the data types and attributes are a little haphazard. I thought it made sense to have Reviewer and Photographer tables. Not every review will have an image, but where images are displayed, I want to display the name of the photographer in the article in the same way that the reviewer is credited. I am a little confused by all of the datatypes - for example I am told that it is best to store images on disk, with their paths in the tables for speed purposes. So...go easy on me if there are glaring errors!
|
|
#13
April 28th, 2005, 11:05 AM
|
||||
|
||||
|
I like name varchar(32) NOT NULL default '',.
It can't be null, but it's empty by default... =) Will it always be one photograph per review? I would suggest a photograph table... Then link photos to reviews the same way you did article_reviewer Speaking of article_reviewer... Judging by your design, I assume that an article can be written by many people? If not, than it's one-to-one. Meaning one article is ever written by one reviewer. In which case, you could put the reviewers ID right in the review table. It's up to you though... I kind of like the one-to-many idea... not sure what your specs are
|
|
#14
April 28th, 2005, 12:12 PM
|
|||
|
|||
|
I'd make all your INT and VARCHAR fields bigger. For VARCHAR, the extra space isn't used, so unless you want to keep an arbitrary maximum character length, just make 'em 255. And for id's, TINYINT is too small -- that'll only get you up to 255 (if it's unsigned). I'd make them unsigned INT.
Also, your date_created timestamps will be changed each time the table's modified. You can change this behavior, but how depends on your MySQL version. Read up on it here.
|
|
#15
April 30th, 2005, 09:12 PM
|
|||
|
|||
|
Quote:
I should correct myself here, for future reference. Space-wise, it doesn't matter what you set your VARCHAR fields to. It does matter, though, if you use GROUP BY or ORDER BY on a field. An ORDER BY on a VARCHAR(255) field will be considerably slower than one on a VARCHAR(50), even if the actual contents are the same length. The difference isn't as pronounced for GROUP BY, but it's also, there.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
