Standard Mysql/Database Practice?

[DomMarx]

Hey guys,

 

I was thinking about this just now. It would be great to get an idea of how you PHP gurus would handle server organization for something like a Facebook-ish site. No, i'm not trying to build a Facebook haha, but I was wondering, since this could apply to many other situations. 

 

Let's say you have a website where users register with their name, e-mail and a password(indeed!). When the users are registered and on the website, they can vote on different posts. Now how would you guys organize this server-side? Would you have a table just for votes, the topics and usernames attached to those votes? Would you have a table per post created with all the users who voted and votes inside?

 

How would you go, or maybe even have gone, about doing this? I'm guessing this is crucial, seeing as how an increase in users and traffic could jam up/crash a server if it's badly organized/set-up. 

 

...and thanks for all the help on here guys. Much better than any other forum i've visited.

Edited June 14, 2013 by DomMarx

[requinix]

A table each for the users, the topics, and the votes.

 

With database normalization (the topic we're on) don't be afraid to be more verbose and split things up, because doing the opposite is actually bad.

[DomMarx]

Hey requinix,

 

So if the users, topics and votes are separated and placed in different tables in a database on the back-end, you can still represent them as one connected group on the front-end? Meaning, even if they are separated on the back-end, you can still present the registered user with a front-end web page listing the topics they voted on in the past? Much like Facebook and many other sites, I can view all of the posts/topics I or any of my friends/other users have ever liked and can even unlike a topic I have previously liked if I change my mind. The username account is always connected to the votes/topics they made/interacted with within their sessions on the site. So placing each of these elements in different tables has no effect to how they connect in the end?

 

Thanks for taking the time out of your day to respond!

[Psycho]

A forum really isn't an appropriate medium to try and learn these advanced skills. Do some reading on database normalization and how to create queries (particularly JOINs). But, to respond to at least your last question, you want data separated into logical tables based upon the "type" of data. One table for all posts for instance. It is how you relate data between tables that is important. For instance, if you have a user table, you should have a unique column for a user_id - this is not the user id that the person uses to log in with , but a unique value int he database (typically an auto-increment value - first user has the ID 1, second user 2, etc.). Then in the POSTs table you will create a record for each post and there should be a column for user_id which is the ID of the user from the user table. Using that you can get all the posts created by a particular user.

[DomMarx]

Hey Psycho,

 

You're absolutely right. My plan isn't to bombard this forum with a billion questions, so don't worry! I'm reading books, searching the web and watching all the tutorial videos I can. I also want to thank you for answering my question. It really helps. Sometimes the web can give contradicting answers all over the place, so I just felt that by asking here I would have the chance to understand things a little better by asking directly/interacting with someone who knows his stuff. 

Thanks guys!

Edited June 14, 2013 by DomMarx

[Psycho]

Hey Psycho,

 

You're absolutely right. My plan isn't to bombard this forum with a billion questions, so don't worry! I'm reading books, searching the web and watching all the tutorial videos I can. I also want to thank you for answering my question. It really helps. Sometimes the web can give contradicting answers all over the place, so I just felt that by asking here I would have the chance to understand things a little better by asking directly/interacting with someone who knows his stuff. 

Thanks guys!

 

Right, but the question was so broad and open ended it is impossible to give an adequate answer without writing a thesis. There are whole books written on the subject of your question. I had actually started a much longer response and ended up removing most of it since it would probably raise more questions than it answered. Go do your research and if you have a question about something, reference the specific article/section and the question you have about it.

[DomMarx]

That's exactly what i'll do Psycho. I spent the morning reading some stuff on normalization and JOINs. What I get from this so far, is that everything should connect through IDs. So if that's the case, one would be better off doing something like this:

 

Table 1- Users: User_ID, Username, E-mail, Password, etc...

Table 2- Posts: Post_ID, Post_Title, Date_Published, etc...

Table 3- Votes: Vote_ID, User_ID, Post_ID.

 

This way I can fetch everything through IDs/Names, generating temporary tables that users can view on the front end when needed without cluttering the database. I'm not saying this is exactly (as i've written it above) the method one should do, i'm just saying that so far from what i've read(only since this morning), this would be a much better/organized way to store/fetch data than having it duplicated in multiple tables. There's still much to learn, and I thank you guys for telling me about this. 

[jcbones]

It sounds like you are on your way to normalisation, at least, in part. Just don't forget that databases can still bog down with a normal dataset. Your queries need to be optimized, and don't forget the indexes, as both can have adverse effects.

[Psycho]

That's exactly what i'll do Psycho. I spent the morning reading some stuff on normalization and JOINs. What I get from this so far, is that everything should connect through IDs. So if that's the case, one would be better off doing something like this:

 

Table 1- Users: User_ID, Username, E-mail, Password, etc...

Table 2- Posts: Post_ID, Post_Title, Date_Published, etc...

Table 3- Votes: Vote_ID, User_ID, Post_ID.

 

This way I can fetch everything through IDs/Names, generating temporary tables that users can view on the front end when needed without cluttering the database. I'm not saying this is exactly (as i've written it above) the method one should do, i'm just saying that so far from what i've read(only since this morning), this would be a much better/organized way to store/fetch data than having it duplicated in multiple tables. There's still much to learn, and I thank you guys for telling me about this. 

 

Your "Posts" table would also need a user_id field to reference the author of the post. Whatever you do - do NOT run queries in loops, which is a common mistake I see. Learn how to properly use JOINs. Tizag is a site with some pretty good tutorials in my opinion. They may not cover absolutely everything, but they provide very short tutorials to teach the bare minimum needed to getting started.Take a look through these two:

http://www.tizag.com/mysqlTutorial/mysqljoins.php

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

 

On second though, go ahead and read through all the MySQL tutorials there. As I said, they are very short.

[gizmola]

You have picked up a lot from the looks of your table design. Having designed scores of databases for all sorts of companies, I can tell you that I find an ER Design tool an essential part of my tool kit, and I always start a project out either with a new ERD (Entity relationship design) or by reverse engineering an existing database. It's much easier for developers on a team to communicate about the implicit design when there's a simple diagram to refer to, and seasoned developers who have to deal with databases usually can read these and understand a lot about the implicit design decisions just by looking at how the tables relate to each other.

 

A lot of these tools tend towards the pricey - 2 I've used in the past typically cost $500-$1k+ (CA Erwin & Embarcadero ER Studio) although in recent years I've been using a product named Dezign for databases which used to be a great bargain, but over the years has crept up to the $300 range. If you're concentrating on MySQL at the moment, then you have the option of using their free http://dev.mysql.com/downloads/tools/workbench/ tool.

 

I highly recommend putting your designs into a tool like this, and all the decent ones then offer the option of having them generate your DDL Sql to create the tables and indexes of your database which can save a lot of time and limit the iteration cycle inherent in design changes or simple mistakes.

 

The other tip I can offer in terms of mysql is to make sure you use the innodb engine for all your tables. Innodb turns on RDBMS standard features like referential integrity and ACID transaction support, and a transaction log that insures the integrity of your database in cases of catastrophic failure, and in most cases when you understand how to resource the database server properly, provides better performance.

[DomMarx]

jcbones, thanks for chiming in! I'm currently reading up on using indexes, and from what I can grasp so far this seems to be a great tool to connect the user, vote and topic entities as a group for things like results in front-end search queries (if i'm not mistaken). I'll keep reading into it!

 

Psycho, i'll make it a priority to remember to never run queries in loops and to do a search on the topic to understand why. Also, i'll certainly read everything through the Tizag.com links you provided me during the upcoming weekend and check out everything they have on MySQL. 

 

gizmola, I re-read your post a couple of times to fully take in the information. I think I understand the basic concept of the tool you are speaking of and it's really intriguing. I'm also looking into this pronto. The engine type is the one thing I haven't played around with in MAMP/PHP Myadmin since really diving into the whole PHP/Mysql world a couple weeks ago, but i'll be sure to use the innodb engine from now on and learn everything about each and every choice in that area. There's so much info to absorb and it's freakin' awesome!  Thank you!

 

 

Thanks guys! 

 

Edited June 14, 2013 by DomMarx