FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySql: More Tables and Less Fields, or the inverse???





Aidmar
Hi!!!

I need make a db to register 1.000 users, and each user will have +- 70 informations. What is faster, make 1.000 tables with 2 or 3 fields, 70 entries and the user name in the table's name, or a table with 1.000 entries and 70 fields, with a field to store the user's name???

Thanks!!!
Peterssidan
You should not create 1000 tables! Wink
the
Aidmar wrote:
a table with 1.000 entries and 70 fields, with a field to store the user's name

That sounds much better. This is the way a database is meant to be used.

Depending on how you are going to use your database it can be efficient to split the fields over more tables. This will require good knowledge on how the database system work so it might be a good idea to just have all fields in the same table.
CTMiB
Peterssidan wrote:
You should not create 1000 tables! Wink
the
Aidmar wrote:
a table with 1.000 entries and 70 fields, with a field to store the user's name

That sounds much better. This is the way a database is meant to be used.

Depending on how you are going to use your database it can be efficient to split the fields over more tables. This will require good knowledge on how the database system work so it might be a good idea to just have all fields in the same table.

Great googly moogly, listen to what Peterssidan says... you do not want to create a separate table for each user. The per-table overhead will take up way too much storage.

As for having a single all-encompassing table versus a few tables to hold various fields, I prefer the latter, but I've gotten used to SQL JOIN statements. Smile
Fire Boar
You can stick several elements together in longer strings if you like, to be interpreted by the PHP (or whatever) code in the script. Explode is your friend.

Generally, more databases is worse than more tables. More tables is worse than more fields. More fields is worse than more records. Better than more records is records with information that you are likely to gather at the same time (like friend 1, friend 2, friend 3, ...) in one field (like "friend 1:friend 2:friend 3:..." all in one). But don't go overboard! Username should definitely be one field, and user ID ought to be there as a primary key too. Just use your discretion together with these tips for a database with an efficient structure.
Peterssidan
Fire Boar, to put a lot of things in the same field is probably not a good idea. The database structure should be adapt to the way it will be used. The example with "friend 1, friend 2, friend 3, ..." in one field I totally disagree to. This is probably a case where you want to create a new table that holds the relation between the two users. Two fields, user and friend which both holds the a user's key value and where both is part of the primary key. In this way it's very easy to handle the data and make joins to other tables.
Agent ME
MySQL is built with heavily optimized code for handling databases efficiently - just put all the data how it should be logically, like a table with all of the users and their information in separated fields, use commands to get just the data you need, and you should be fine. (If you need to look up user #2's favorite color, don't do "SELECT * FROM users WHERE id=2", but "SELECT color FROM users WHERE id=2" [it's been a while since I've used sql, so that command might not be exactly right but you see where I'm going with it])
Aidmar
Thanks for all!!!

It was very useful, and forgive my bad, bad english... I will do like AgentMe says, and when i finish my project i will try make some "experiments".

Thank you!!!
polly-gone
Wait..... do you mean something like have a table with 70+ fields for each user, or do you mean have tables separated by category. Like for an RPG have a user id and login info table, and then each specific things like items owned etc is in a different table all hooked together by the user id?

If it is the second one you are referring to, that would be much better.

-Nick Smile Smile Smile
manav
balance d two...

redundent data in different table might consume space but then again it gives good performance...

but too many tables just kill the purpose.... and too many redundent fields too..

so the choice depends from application to application Very Happy
Related topics
Question about MySQL!
How To : Improve Your PHP Programming
Forum
How can i flush my tables
Anyone use MySQL Administrator 1.1.2?
PhpMyAdmin
[php]putting script fragments together?
Price tag on this:
Poll in html
PHP Script needed
Paying $30 for mods installation!!
Rules Enforcement
Changing PhpBB
convert the entire database's collation to utf8_general_ci
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.