MySQL Database organization question

I know my PHP but I am new at databases. I wanted to know how you would organize a database of users for an app that is supposed to hold a lot of information per user.

For example, if every user has a bunch of clusters of data that need to be stored, and they can always add more so it needs to be dynamic. What would your organization be for this?

Is this correct?
A table holds all users, and every user gets a table of their clusters?

A table per user is a plan for disaster. Hard to manage, messy, hack-ish, etc.
The way to do this I'd say is to take the id field of the user (you have one, right?), and use it as a reference/link to that user in another table that contains the data.
This way it is dynamic, easy to work with (just do a SELECT * FROM `userclusters` WHERE `userID`='$uid'), and effective to manage. Obviously this is overkill for things like email and MSN contact name, but is needed for things where, say, users upload and store files to a website.

is it okay to have a table that big though? and thanks much for your help.

I'd have to look into it, but theoretically databases are designed for relatively few tables, but those tables having massive datasets in them (I've got a 16mb mysql table that handles just fine).

great thanks!

You should be looking to keep your database fairly simple in structure to condense your scripts so they are also easy to understand.

Hi,

In general putting everything into one table is not good database design. Personally, I like my databases to be rather normalized (i.e. do not contain redundant data, contain tables with data only specific to the context of the table etc etc).

If you go here: http://dev.mysql.com/doc/refman/5.0/en/full-table.html it will tell you what the limits are on a table.

It really depends on what you are storing in that data - but also keep in mind that the larger the table size, the longer it will take to search. If you have a set of tables that is logically designed than you are using a "divide-and-conquer" approach when querying your database, which would be faster than looking through one single table with tons of data.

Like cpfx.ca said though, a table per user is not a good approach, what you could have however is a table of users, clusters and user_clusters (which would link up the user to their cluster). This way, the users table contains only info pertaining to users, clusters contain only the blobs of data and user_clusters maps each user to one or more clusters (or you can even map one cluster to many users) - sky is the limit

Good luck!

Mike

Good advice above. The way I think about it goes something like this:

OK, I've got a bunch of users, and every user has a bunch of attributes. Some of those attributes can only occur one at a time (things like userID, first name, last name, username, password, etc.) Those can be listed as fields in a single table with no problems.

Users may have variable numbers of other attributes, though. For example, every user on this forum has a different number of posts. So rather than building a table of posts for each user, you build one table called Posts and one of the fields contains the ID of the user who wrote the post. In this way, by using 2 tables, a single user can be related to any number of posts.

There may be other instances when the flexibility needs to go both ways. I ran into this when building a CMS recently. A user may have permission to edit any number of pages, and a single page may need to be edited by any number of users. In a case like this, I build 3 tables: 1 for Users, 1 for Pages, and 1 for Permissions. In the permissions table you have 3 fields: A Permission ID, a Page ID that references the Pages table, and a User ID that references the Users table.

There are probably occasionally instances where a more complicated set-up is needed, but I'd say one of these scenarios will take care of you 99% of the time.