Another beginner needs advice..

Hello,
I'm new to the forum so please give a hand:)
I've been working with php/MySQL for about 6 months, just trying out new stuff and so, but now I feel ready to build a REAL site!

I'd like to make one of those community sites where everyone has their profile, personal messages, gallery (with tags and gallery folders), a blog and a list of people that viewed the users profile / list of profiles that the user viewed.

What's bugging me is the DB design. Should I:

A) Make a few GLOBAL tables (e.g. gallery, image_list, user_list, viewed_profiles, blog, tags..) in which I would store the info of EVERY user (indentified by their ID), or

B) Make all of the tables mentioned above (e.g. gallery, image_list, user_list, viewed_profiles, blog, tags and so on) for every user INDIVIDUALY (e.g. user John would have tables: john_gallery, john_blog, john_tags).

I'm asking this because I'm worried about speed. What if the site has 10 000 or more users?
In case A that would mean having millions of messages in a single table and being very slow.
In case B I will probbably end up with hundreds of thousands of tables in my DB.

Or is there a solution C?:-O

Help:)

Just wondering if you could squeeze this in lesser number of tables. eg: put the user info in table-1 - (id / name / address / profile details / main picture ref etc), put optional images in table-2, thread details in table-3 and so on..

Dont forget to normalise the tables though.

Hope this helps!

There is no need to give each user their own table. 10,000 users would mean 10,000 tables. That sounds ugly to manage.

Also, big tables are nothing to fear. Proper indexing and crafted queries can reduce a lot of performance issues.

yes, having too many tables causes issues with MySQL (i think there is some sort of performance barrier)

option A for sure

Thanks! I'll post a link once I finish the site!:)

Yes, too many tables would be a problem.

I think, there are also few free open source scripts available, get them and study them. In fact a part of scripts for forums such as phpBB will also help you to understand how to solve this problem.

Dear ivanfx, dont get me wrong, but any given book you pick up, related to databases on the first page it will state that there are 2 types of databases - relational and flat-file. Relational databases are famous for their ability to bring flexible and higly cohesive and loosely coupled solutions to dataflow. Now, think of it this way: in relational database you enter a record once, and you identify it. For example, you have a record for user John, and this record is associated with UserId = 1 (the field name and the value could be anything). From this point on, this user John will be represented as UserId 1 throughout your database. This saves space and time. Hope this helps.