organization

[nitch]

simple question.  i am creating a site with a user system.  Each user will have their own page and be able to create their own information in different categories that will be stored on the database.  the only way i can think of to organize it is to have a separate table for each category and put every user's information into the table.  My question is a) could this cause significantly high load times as many users join and add information and b) if so is there a better solution?

 

sorry if thats confusing thanks for any help

[btherl]

I would start with a structure like this:

 

Table users

user_id - integer, automatically incrementing

user_name - varchar

email - varchar

etc etc .. with all other information you want to keep for the user

 

Table categories

category_id - integer, automatically incrementing

category_name - varchar

and other information for each category

 

Table content

content_id - integer, automatically incrementing

user_id - owner of content

category_id - category of content

content - varchar

and other information regarding a piece of content

 

 

That structure makes it easy to add categories and users without creating new tables.

[nitch]

hmm i dont think i explained my question very well.  That is the structure i have come up with.  Say i have a users table filled with users then i have 3 content tables content1, content2, and content3.  Each user will be able to add information to their account which add a row to one of the content tables.  If i had a large number of users with each user adding multiple rows to each of the content tables would this cause the script to slow down significantly? and is there a better solution?

[Kasuke_Akira]

In time, it would of course slow down.  However, imo, (though I'm not some databse structure guru)  your structure looks decent enough.  The more you can spread the information into other tables, the better off you are going to be with load times if you are only accessing certain tables at a time and not all of them.

[btherl]

I doubt you will see any slowdown unless you have hundreds or thousands of users creating content simultaneously.  I imagine any one user could not produce more than 1 piece of content every minute, and even that's being optimistic.

 

The structure I am suggesting is not the same as yours.  I am suggesting both a categories table AND a content table, but no others.  Instead of content1, content2 and content3 tables, I am suggesting a categories table containing the identifiers 1, 2 and 3, and a single content table containing all content.  My suggestion is more flexible, but yours may well be faster.  Seperating tables often gives speed benefits, at the cost of flexibility.

 

Can I clarify that your content1, content2 and content3 tables store different kinds of content?  Or are they a kind of load balancing system to store the same kind of content in seperate tables?

[nitch]

ah sorry, i didnt understand your structure at first.  you are correct that the tables store different content.  In this case there would only be 3 or 4 different content tables that a user would be able to add information to so i don't believe having a separate categories table would provide a significant advantage.  it would definitely be nice if this site was producing a large enough userbase to have hundreds trying to access at once but that is definitely optimistic.  i suppose i could create a table of content for every user that joins but that seems unnecessary.