browno Posted January 6, 2006 Share Posted January 6, 2006 Hello! I'm really needing some help. I'm currently trying to create a website dedicated to the local music scene in my area. I want to have a database containing as much info as possible about the artist (name, where they're from, a biography, interviews) as well as info about their releases (titles, credits, single or album). I'd also like to have tracklistings from these releases that the user can click on for individual song info and lyrics. So... I've setup a preliminary database with three tables: artists, releases and songs. ARTISTS artistid - smallint, length (6), Key (Primary), Default (NULL) artistname - varchar (70), Key (Mul?) origin - varchar (40) yearformed - year, length (4) biography - longtext website - varchar (40) interview - longtext artistimage - varchar (40) artisttype - char (1) RELEASES releaseid - smallint, length (6), Key (Primary), Default (NULL) reltitle - varchar, length (50) format - varchar, length (20) label - varchar, length (30) year - year, length (4) credits - mediumtext description - mediumtext artwork - varchar, length (30) artistname - varchar, length (70) SONGS songid - int, length (11), Key (Primary), Default (NULL) songtitle - varchar, length (50) trackno - tinyint, length (4) composer - varchar (80) performer - varchar (50) lyrics - mediumtext reltitle - varchar (50) I hope you can see what I'm trying to do. On the website you click on an artist, you get their bio and a discography. You can click on an album in the discography which will show you the tracklisting for that album. You can then click on that song to get lyrics or whatever. So, by not really knowing what I'm doing, is my design ok? artist and release are joined by artistname and releases and songs are joined by reltitle. Also, do I have to declare my intended lengths for varchars. Does it improve the efficiency of the DB in anyway? Thanks so much for your help!! Quote Link to comment Share on other sites More sharing options...
cmgmyr Posted January 6, 2006 Share Posted January 6, 2006 I just looked through this real quick and here are some changes that I think you should make: 1. In Table_Releases replace artistname with artistid (same as in Table_Artists) You can always join the 2 together, this will save you space and also keep everything connected by a number and not text, not that it matters too much but I think it's more reliable. 2. in Table_Songs replace reltitle with releaseid (same as Table_Realeases)...for the same reason as above. Those are only minor changes for you...but I think that they would help you in the long run. Everything else looks pretty good. Do you have the domain set up for this already, I would like to watch your progress with this and I am interested in visiting the site when you are done with it. I have also made a local music site for syracuse,ny at www.syracusebands.net check it out, and if you need any other help, please let me know. -Chris Quote Link to comment Share on other sites More sharing options...
fenway Posted January 6, 2006 Share Posted January 6, 2006 A few suggestions: 1. As indicated above, you should only use UIDs as foreign keys in tables -- this allows you to change any column in the original table without having to worry about it. Furthermore, ALWAYS make your UIDs "bigint(20) unsigned" -- I know it doesn't seem like a big deal now, but why worry about it later if your DB grows? 2. You might as well use LONGTEXT instead of MEDIUMTEXT -- there's [a href=\"http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html\" target=\"_blank\"]not much of a difference[/a], and you won't ever have to worry about it ever again. 3. You should have some of your tinyint fields as "unsigned", so that you double the max. value you can store (e.g. 255 vs 127). 4. If you're already using VARCHAR, you might as well specify the largest column size (255), because otherwise you're just limiting your flexibility for no reason. 5. "artisttype char(1)" -- this is a bad idea. Use an ENUM field if you want to use "codes" -- and if it can grow beyond 64 options, make it a separate table. 6. You should be storing "year" as a DATE field, as rename the column to "dateformed", for example. What if you ever want to include a complete date? 7. I don't see very much info regarding indices -- this really depends on queries, but an index on column like name make sense, and probably a fulltext index on lryics for sure. 8. Make sure you specify all your columns as NOT NULL explicitly, unless you have a good reason not too. Hope that makes sense -- feel free to ask for any clarification on these points. Good luck. Quote Link to comment Share on other sites More sharing options...
browno Posted January 7, 2006 Author Share Posted January 7, 2006 Thank you both for replying! It's now looking like this (with most fields not null): ARTISTS artistid - bigint(20), unsigned, Key (Primary), Default (NULL), auto_increment artistname - text origin - text dateformed - date biography - longtext website - text interview - longtext artistimage - text artisttype - enum RELEASES releaseid - bigint(20), unsigned, Key (Primary), Default (NULL), auto_increment reltitle - text format - text label - text datereleased - date credits - text description - text artwork - text artistid - bigint(20) SONGS songid - bigint(20), unsigned, Key (Primary), Default (NULL), auto_increment songtitle - text trackno - tinyint(4), unsigned composer - text performer - text lyrics - text releaseid - bigint(20) fenway - my artisttype was going filled with: B for a band, P for a person. How would enum improve this? how would i use it? similar way? I'm also not sure what you mean by indices. How and what should I specify and why? Thanks!!!! cmgmyr - no domain yet, just messing about on my local machine. thanks for the interest! hope I get my site going as well as yours! how do get the top 10 bands and 5 newest bands? Great feature! Thanks again to both of you! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 8, 2006 Share Posted January 8, 2006 A few comments -- first, you don't need TEXT columns for everything, just the ones you expect to be _really_ long (like lyrics) but not song_title, which isn't going to be more than 255 characters ever. Second, you missed a few "unsigned" attributes for the foreign keys. Third, you don't need a default NULL for the auto_increment columns; it doesn't even make any sense to have it there. Fourth, for the artisttype, simply declare an enum with ('person','band') as the options -- you don't need to use a "code" just because you want to keep the column tiny. Lastly, an index is used to optimize searching the tables -- there are many places you can read about this. Quote Link to comment Share on other sites More sharing options...
browno Posted January 8, 2006 Author Share Posted January 8, 2006 Another version, can't thank you enough! ARTISTS artistid - bigint(20), unsigned, Key (Primary), auto_increment artistname - text origin - varchar (50) dateformed - date biography - longtext website - varchar (60) interview - longtext artistimage - varchar(40) artisttype - enum RELEASES releaseid - bigint(20), unsigned, Key (Primary), auto_increment reltitle - varchar, length (60) format - enum label - varchar (50) datereleased - date credits - text description - text artwork - text artistid - bigint(20), unsigned, Key (MUL) SONGS songid - bigint(20), unsigned, Key (Primary), auto_increment songtitle - varchar (80) trackno - tinyint(4), unsigned composer - varchar (50) artistid - bigint(20), unsigned, Key (MUL) (Take this out as can be found through releaseid?) lyrics - text releaseid - bigint(20), unsigned, KEY (MUL), Default (NULL) I've also replaced performer with artistid, although does this need to be here at all if it can be joined through releseid? Will look up indices! Thanks again! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 8, 2006 Share Posted January 8, 2006 Much better... but you forgot about one of my earlier comments: [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]4. If you're already using VARCHAR, you might as well specify the largest column size (255), because otherwise you're just limiting your flexibility for no reason. What I meant here was not to pick a bigger column _type_, but rather to use VARCHAR(255) for all of your VARCHAR columns. It looks to me like you've picked arbitrary lengths, and you're just going to run into problems that way. The whole idea of a VARCHAR is that you're saving storage space (i.e. only the size of the actual text in the field), so why not have the max length? Also, you are correct -- if you can simply look up the artistID from the releaseID, then don't store them both in the songs table. Good luck. Quote Link to comment Share on other sites More sharing options...
browno Posted January 8, 2006 Author Share Posted January 8, 2006 Great - varchar(255) for all my varchars! Last question! Is my tinyint(4) unsigned ok for trackno? can I go even smaller? I'm assuming that no-one will have more than say 200 tracks on a release (like if it was a box set or something). Your help is so appreciated. Thanks again! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2006 Share Posted January 9, 2006 A total of 255 tracks seems reasonable to me, but if you're worried, go to MEDIUMINT. Quote Link to comment Share on other sites More sharing options...
browno Posted January 9, 2006 Author Share Posted January 9, 2006 [!--quoteo(post=334645:date=Jan 9 2006, 12:42 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 12:42 AM) 334645[/snapback][/div][div class=\'quotemain\'][!--quotec--] A total of 255 tracks seems reasonable to me, but if you're worried, go to MEDIUMINT. Is that what tinyint (4) is giving me, a max of 255 tracks? Also, sorry to be annoying but I'm having difficulty with figuring out index stuff. Am I adding an additional table for indices which hold the most unique values of the table as well as a few other columns of the indexed table? Sorry if this is a really silly question, but will my database benefit from indices? Thank you!!!! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2006 Share Posted January 9, 2006 You can look at the various numeric types [a href=\"http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html\" target=\"_blank\"]at this refman page[/a]. If you have to ask that question about indices, then I would refer you to [a href=\"http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html\" target=\"_blank\"]this refman page[/a] as well. There is no such this as an ®DMBS design that does not include indices. To a first approximation, you need to index anything that you're going to lookup. Quote Link to comment Share on other sites More sharing options...
browno Posted January 9, 2006 Author Share Posted January 9, 2006 Great. Thanks again for all your help! Quote Link to comment Share on other sites More sharing options...
browno Posted January 9, 2006 Author Share Posted January 9, 2006 Ok, sorry! One more question! I'm wanting to achieve the following scenario: If the user selects an band on the site then all columns will be pulled out of my artist table. However, I'm also wanting the user to see individual members of this band on the same page. Should I just add a 'memberof' column (artistid) to my artist table so that the if the artist in question is a band then the memberof column is searched for the artistid of the original band? Let me know if I'm not being clear enough. Thanks! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2006 Share Posted January 9, 2006 Sounds like you need a "bands" table -- with the name and other details -- and then include a band_id foreign key column in the artists table. This is, of course, assuming that an artist cannot be a member of two bands; otherwise, you'll need a 3rd lookup table. EDIT: I think I'm confused -- aren't the artists the bands? If so, where are the "members"? Quote Link to comment Share on other sites More sharing options...
browno Posted January 9, 2006 Author Share Posted January 9, 2006 [!--quoteo(post=334852:date=Jan 9 2006, 04:30 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 04:30 PM) 334852[/snapback][/div][div class=\'quotemain\'][!--quotec--] EDIT: I think I'm confused -- aren't the artists the bands? If so, where are the "members"? There will be bands and solo artists. Although a member in a band could also be a solo artist. As well as playing in more than one band. That any clearer?! Hope so! Quote Link to comment Share on other sites More sharing options...
cmgmyr Posted January 9, 2006 Share Posted January 9, 2006 Hey again! Sorry I didn't reply sooner...I had a pretty busy weekend. I'm glad you liked how I did the top 10 and recent 5. For the top 10: I have a stats table in the database. Each band that has made a profile has and entry in the table. Every time someone clicks on their profile their stats are increased by 1. At the end of my query it is "... ORDER BY hits DESC LIMIT 10" For the 5 newest: When the bands sign up there is a start date for each entry so my query includes "... ORDER BY start_date DESC LIMIT 5" Pretty simple stuff, but it looks cool to the viewer. Let me know if you would like to know anything else. -Chris Quote Link to comment Share on other sites More sharing options...
browno Posted January 9, 2006 Author Share Posted January 9, 2006 Do you have the start date also in your stat table and just join it by the band's id? Or do you have it in the band table? Thanks again! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2006 Share Posted January 9, 2006 In that case, I think you still need a bands table, and some of the fields that currently reside in the artists table may need to be moved. Quote Link to comment Share on other sites More sharing options...
browno Posted January 9, 2006 Author Share Posted January 9, 2006 [!--quoteo(post=334971:date=Jan 9 2006, 10:59 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 10:59 PM) 334971[/snapback][/div][div class=\'quotemain\'][!--quotec--] In that case, I think you still need a bands table, and some of the fields that currently reside in the artists table may need to be moved. I was going to have the artist table have both bands and solo artists within it but add a memberof column. If the memberof column was filled with the band that a solo artist was in then this would be displayed, but this wouldn't allow the artist to be in more than one band. Am I better splitting solo artists and bands into two seperate tables? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2006 Share Posted January 9, 2006 You should definitely have two separate tables, no question about that. Keep the stuff that pertains to each separate, it'll make your life easier in the long run. Quote Link to comment Share on other sites More sharing options...
browno Posted January 10, 2006 Author Share Posted January 10, 2006 [!--quoteo(post=334975:date=Jan 9 2006, 11:34 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 9 2006, 11:34 PM) 334975[/snapback][/div][div class=\'quotemain\'][!--quotec--] You should definitely have two separate tables, no question about that. Keep the stuff that pertains to each separate, it'll make your life easier in the long run. Ok, so I now have this: ARTISTS artistid - bigint(20), unsigned, Key (Primary), auto_increment biography - longtext interview – longtext website - varchar (255) artistimage - varchar(255) BANDS bandid - bigint(20), unsigned, Key (Primary), auto_increment bandname – text placeformed – varchar(255) dateformed – date artistid - bigint(20), unsigned, Key (Primary), auto_increment SOLOS solosid - bigint(20), unsigned, Key (Primary), auto_increment soloname – text placeborn – varchar(255) dateborn – date artistid - bigint(20), unsigned, Key (Primary), auto_increment RELEASES releaseid - bigint(20), unsigned, Key (Primary), auto_increment reltitle - varchar, length (255) format - enum label - varchar (255) datereleased - date credits - text description - text artwork – varchar (255) artistid - bigint(20), unsigned, Key (MUL) SONGS songid - bigint(20), unsigned, Key (Primary), auto_increment songtitle - varchar (255) trackno - tinyint(4), unsigned composer - varchar (255) artistid - bigint(20), unsigned, Key (MUL) (Take this out as can be found through releaseid?) lyrics - text releaseid - bigint(20), unsigned, KEY (MUL), Default (NULL) I can't see how I would join an artist to a band or even to a number of bands. hmm. Sorry for being rubbish! Didn't mean for this topic to go on for so long! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 10, 2006 Share Posted January 10, 2006 First, you have it somewhat backwards -- the bandid should be in the artists table, not vice versa. Second, if you want a one-to-many relationship (i.e. multiple bands per artist), then instead of having the bandid in the artists table, you'll need a make a new table with the uids from both the relevant tables. Third, IMHO, there are still too many TEXT fields. Quote Link to comment Share on other sites More sharing options...
browno Posted January 10, 2006 Author Share Posted January 10, 2006 [!--quoteo(post=335080:date=Jan 10 2006, 06:53 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 10 2006, 06:53 AM) 335080[/snapback][/div][div class=\'quotemain\'][!--quotec--] First, you have it somewhat backwards -- the bandid should be in the artists table, not vice versa. Second, if you want a one-to-many relationship (i.e. multiple bands per artist), then instead of having the bandid in the artists table, you'll need a make a new table with the uids from both the relevant tables. Third, IMHO, there are still too many TEXT fields. What if the artist isn't a band but a solo artist? That's why I did it that way. Sorry, I'm confused! I've also taken out the TEXTs for artist/band/solo names and repleaced them with VARCHAR(255). I've left longtext for biographies and interviews, and text for album credits and descriptions. Sound ok? Again, thank you. Quote Link to comment Share on other sites More sharing options...
cmgmyr Posted January 10, 2006 Share Posted January 10, 2006 No, the start date is in the band's table there is a join between the two. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 10, 2006 Share Posted January 10, 2006 [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]What if the artist isn't a band but a solo artist? Then simply don't put in a band_uid -- what's the problem? Alternatively, since a solo artist is still a "one-man band", there's nothing wrong with having a record in both tables. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.