views:

220

answers:

3

I am developing a website that has user profiles, accounts, account settings, inboxes, etc. just like Facebook or LinkedIn and I'm wondering how to set up the mySQL tables for it.

Should I create a table for every user for each function (profile, inbox, etc.)?

A: 

You wouldn't create a table for every user.

You would create a record and a unique id for each user, and then relate that user to other tables using the ID as a foreign key in other tables.

Jack Marchetti
A: 

In short yes, each function should be in one table, if there is aneed to. On edit: And not a table for every user. One record in each corresponding table

The concept this deals with is normalization. Consider profile and inbox. A user has only one profile, but would have numerous messages in the inbox. It makes sense to have the inbox as another table with each message being identified by a message-id and its owner, then its content.

Extrakun
+2  A: 

You really need to think about everything you put into a database in terms of relationships (as mentioned by other posters). In the example you gave a user is going to have a one to many relationship with things like mail, they will have a one to one relationship with their user profile, and they will have a many to one relationship with something like 'locations' where many users live in one city.

As you are designing your database always ask yourself which relationship type you need to build and make an attempt to never duplicate data in multiple tables when you don't need to.

When you set up something like a users table make sure that every row is equal to a user (as opposed to every user being a table) and that every row has a unique auto-increment id. This user id (that is generated by MySQL) is going to be the reference that you use in other tables to link your data together.

So when you setup your mail table you might have 15 rows containing email data, but each of those rows will contain a field named something like user_id that will contain the unique id of that user from the users table.

Start getting familiar with things like LEFT JOIN--this is how you will run a single query and get, for example, your user's data AND all of his email at once.

angryCodeMonkey