3

I am working on a PHP web application with a backend MySQL Database. Long story short, each user has 50 characteristics that NEED to be stored.

To make the queries as fast and efficient as possible, should I give each user their own table with 1 column and 50 rows OR should I put all users in one table with 51 columns (1 for username, 50 for the characteristic variables).

These are the only two solutions I have come up with. Any other advice would be greatly appreciated! Thank you in advance.

user2974848
  • 45
  • 6
  • 5
    _give each user their own table_ Never take this option – RiggsFolly Jun 22 '18 at 15:02
  • There is also the One user table and one linked characteristics table – RiggsFolly Jun 22 '18 at 15:03
  • 1
    If all 50 parameters will be filled for every user, just stick them all into one table – Machavity Jun 22 '18 at 15:03
  • 1
    1 table with 51 columns would be better - **however** if not all data is required it might be better to have a *userdata* table to hold that data as key => value pairs and a link table *user_to_userdata* that just holds the primary keys from both tables (linking them together); look up "third normal form" – CD001 Jun 22 '18 at 15:03
  • Could also go down the `EAV` route. – Hudson Jun 22 '18 at 15:09
  • 1
    @Hudson - full-blown `EAV` is a bit of a double-edged sword though; it's nicely normalised but it can (in the case of Magento for example) make queries more complex and affect performance (meaning you then need to create non-normalised "flat" tables... like Magento again). – CD001 Jun 22 '18 at 15:13
  • 1
    As long as he limits his EAV usage to everything necessary, it shouldn't be too bad and magento is a nightmare, I'm a magento developer. It has great concepts, but EAV can be good. There's great applications for it. – Hudson Jun 22 '18 at 15:15
  • IMHO what @CD001 is suggesting suits better if you have only some of the 50 features for each user. – Lelio Faieta Jun 22 '18 at 16:18

1 Answers1

0

If you follow database normalization: You make one user table, with 50+ columns.

This way you can query users, filter on any characteristic you need.

If you want to make your queries faster:

  • make sure you have a PK
  • make indices where necessary
  • select only the columns you need in your code. (do not SELECT *)
rauwebieten
  • 149
  • 7