Tuskony Posted October 20, 2008 Share Posted October 20, 2008 Hey guys I have a few questions about designing a database for a website I am making. The website is like any website in that is has user accounts and people can log in and gain access to other areas of the site, yadi yadi ya. My question lies in that on my site users can create "trucks" (it's a website dedicated to hobby trucks). Each truck consists of some information but more importantly it consists of pictures! But I don't know how many pictures! And each picture in the users "photo gallery" can have comments attached to it by other users. Comments that need to include information like who posted it, when, and their comment. At first I was just using a single database for the trucks and each truck contained a column called "Pics" and I just used explode() to extract all the pictures off a single line. However now I am not sure what is my best solution for implementing the comments? Do I need to create a whole seperate database for each truck created that simply contains that truck's comments? IE database: truck_gallery_53_comments Or can I some how incorporate the comments into the truck database structure itself? Or do I have a giant database for ALL the comments on all pictures and then when a pictures if viewed I pull all matching comments? I hope this makes sense! Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 20, 2008 Author Share Posted October 20, 2008 Or should I have a database created for each truck that's created then have that database contain a image table, comments table, etc? Quote Link to comment Share on other sites More sharing options...
Bendude14 Posted October 20, 2008 Share Posted October 20, 2008 I would have a table called trucks. Then store all information in that table such as images comments etc. And most importantly save the User ID from the users table as the Foreign key in this table. Then you will have everyones trucks in one table but still be able to related them back to each individual. Ben Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 20, 2008 Author Share Posted October 20, 2008 How would I store the truck imormation / comments all in one table? What would the structure look like? Remember I don't know obv how many comments each picture will get etc. Quote Link to comment Share on other sites More sharing options...
Bendude14 Posted October 20, 2008 Share Posted October 20, 2008 if the site is getting big then your better having more tables early on. So you would have a table for comments a table for trucks and a table for users. Can each truck have more than one user/owner? If so you could even consider having different tables for different types of trucks. Read up about normalization in google. heres a good start http://en.wikipedia.org/wiki/Database_normalization Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 20, 2008 Author Share Posted October 20, 2008 Nope each truck has 1 owner. And Yes i think i'm gonna go with the multiple tables in one database! Thanks man. Quote Link to comment Share on other sites More sharing options...
Maq Posted October 20, 2008 Share Posted October 20, 2008 Nope each truck has 1 owner. And Yes i think i'm gonna go with the multiple tables in one database! Thanks man. You kind of have to have more than 1 table in a database or it's going be impossible for you to pull out information. You should really google a good design example and maybe implement it into yours. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 20, 2008 Share Posted October 20, 2008 Read up about normalization in google. There's a whole sticky dedicated to this topic on this forum. Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 20, 2008 Author Share Posted October 20, 2008 Maq > I'm not sure what you mean? Here is my current design: MySite (main database for the whole site) ---> Users (table for all accounts) ---> Trucks (table for all the created trucks) ---> Images (table for all the images associated with the trucks) ---> Image_Comments (table for all comments associated with each image). Does that sound about right? Fenway > Sorry!!!!! Is this topic called normalization or w/e? Quote Link to comment Share on other sites More sharing options...
Maq Posted October 20, 2008 Share Posted October 20, 2008 Please read normalization. You said: And Yes i think i'm gonna go with the multiple tables in one database! which implies that you are only using 1 or were thinking about only using 1 table. I was telling you that in order for you to have a high level of normalization you need to have more than 1 table in your situation. Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 21, 2008 Author Share Posted October 21, 2008 Ok I read a few of those normalization topics but I'm a bit confused as to how they'll help me with my database/tables.... Do I somehow need to link my user account with my image and comment tables? Right now I just use Primary Keys for each value in each table and each image has a field "AccountID" and "CommentID", and transversly the comment table has a "ImageID", and "AccountID" column. Is this correct? Quote Link to comment Share on other sites More sharing options...
Bendude14 Posted October 22, 2008 Share Posted October 22, 2008 yes but your primary key in one table show be a foreign key in another table IF the two tables are linked... Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 22, 2008 Author Share Posted October 22, 2008 Well that's kind of my question.... should my tables be linked in this situation? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 22, 2008 Share Posted October 22, 2008 I'd have thought a hierarchy like this [pre] user truck image comment ---------- ---------- ------------- -------------- userID ----+ truckID ----+ imageID ---+ commentID username | truckdata | imagepath | comment etc +----< userID +---< truckID +----< imageID | +------------------------------------------------< comment_by [/pre] Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 22, 2008 Author Share Posted October 22, 2008 Barand > That is basically exactly how I have my tables setup!!! So how should I be querying the database? Should I be joining tables or doing that normalization stuff? Or am I good just to query the database with good ole "select * from <table> where <clause>"? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2008 Share Posted October 23, 2008 Surely, you mean "Terrible ole "select *". As to whether to join or not depends on the information you want from the db. In the schema I proposed, if you want the user who owns the truck in the image then of course you need joins. Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 23, 2008 Author Share Posted October 23, 2008 So joining is basically a way of keeping each table a little bit trimmer and less bulky by not having redudant fields? Instead you sort of link tables togeather using joins? What's a better way to query the table other then select * when you don't need a join? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2008 Share Posted October 23, 2008 specify the column names you need instead of *, whether you have joins or not Quote Link to comment Share on other sites More sharing options...
Tuskony Posted October 24, 2008 Author Share Posted October 24, 2008 Oh ok yeah I do do that. I don't ALWAYS use select * Thanks for your help. 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.