Multi Users table

[SHAD]

Is it a good idea to divide the users table in to 2 or 3 tables, only if your current table has too many columns? Any advantage/Disadvantages of doing this?

[corruptcity || skalman]

m8 dont get me started on multi users tables for 1 of my foundation degree in ICT assigments was a www/internet project where had to build a help desk website using UML diagrams from my database unit and i ended up with 2 users tables and had ambiguous userids and had to spend hours fixing that and in the end ended up creating a user_login table that was to sit above the 2 user tables which gave the tables their ids

[sniko]

* I don't do jargon, so i'll speak plain english

Personally, I use multiple tables for user accounts, for different aspects on the user, for instance;

• One for login details (IP, Email etc)
  
• One for in-game information (Money etc)
  
• One for user statistics (Fighting abilities)
  

 

But, only to make it easier for myself to edit things within phpmyadmin. You would have to join the tables together in a mysql query. As well as referencing them correctly, so you can easily cross-reference the tables with each individual user.

I'm not too experienced with testing the speed, but i've heard it helps it, not sure how.

Anyway i hope that helps

-sniko

[rulerofzu]

It all comes down to structure.

If your mysql database and its tables are structured well and you have employed a sound understanding of mysql into the tables fields then joining several tables can be quicker than using one large table.

Also depends on what you need to do with the data and of course how good your code is.

I should imagine Alain has more knowledge on this than others

Guide to Database Performance

[a_bertrand]

one to one table design as you are proposing (basically splitting a table on multiple tables due to the column number) is not a good idea.

1) Each time you need to read columns on both tables a join need to be done, even if a join on primary keys (like the userid) are fast they are certainly slower than reading all in shot.

2) What speed ups more is to select ONLY the required columns and not all via a *

3) Using split tables WITHOUT using foreign keys will have the effect that you quiet certainly end up with a table having users which are not anymore linked to the other. MyIsam doesn't support foreign keys which means you will need to use for example InnoDB, which requires different tweaking on the mysql server.

So overall it's a very bad idea ;)

[Dominion]

Well it depends what you mean...

For example if you have 10 columns for one mod, only used on 2 pages, and is still selected, then yes consider putting that into a separate table. Not every mod needs to insert into the “users” table: most old free mods do. Try having a quick look here - New tables are good! Database

Of course A_B does have a point; if all you’re going to do is use a join to select the information, as it’s still needed, then yes it’s pointless.

[a_bertrand]

If you use some data only only on very few cases, then yes it may help to split the data... but think carefully.

What is sure, is that I would personally avoid to have 200 or more columns and yet more important what's the total size of a row. The smaller the better, but don't go to designs which will force you to make joins too many times.