Database question/discussion

[Dark57]

So I'm looking to use a database to store many different fields of information.  I'm looking at roughly 20 or more columns of information per user just at the start, before I start getting into other systems I may implement in the future.

 

My question is, would it be easier to manage all of this information in multiple tables or in one huge table.  Whats the downside to using one huge table and the downside of multiple tables.  Will either of these slow down the website or use more bandwidth?

 

 

EDIT: Actually, now that I think about it this should probably be in the MYSQL section.

[litebearer]

might start here...

http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/

[Dark57]

Yeah well I said its a question/discussion because I'd like to hear from peoples personal experience.  Thanks for the link though, I will read it.

[ignace]

The best practice is to use multiple tables as it

 

1) separates the data in ordered groups

2) removes overhead (when you enter product info you don't want to specify a new user)

3) searches are faster (even if not indexed) as the data is in smaller chunks (1000 products, 10 users would search through 1010 rows upon user login while it would search through only 10 rows if contained in it's own separate table).

 

Reason number 2 should be enough to convince you to create multiple tables and properly normalize your database.

 

EDIT: Actually, now that I think about it this should probably be in the MYSQL section.

 

Good thinking.

[Dark57]

Ok I do see the logic behind creating multiple tables inside a database.  I am reading up on normalizing it and it makes sense, now I will just have to take it to the drawing board so I can break it down into tables that make sense.

[DavidAM]

Or, you could continue this discussion by describing the "columns" of data you are proposing and we can help you break it up into tables, if necessary.

 

ignace's statement to use multiple tables may or may not be correct.  If all of the columns are specific data about the user, a single table could be appropriate.  If the columns include products purchased or other data not specific to the user, then there are multiple tables in the database.  However,  I can easily see a user table with 20 or more columns of user-specific data.  Without knowing what the proposed data elements are, and the proposed system, we can't really answer the question.  Ordinarily, there should be one AND ONLY one USER table with each user listed in that table.