I've 3 MySQL tables for any membership system.

  • users: Minimum requirement to become a user, only associated with account info (email, password, is_triggered, etc)
  • user_profiles: Private information supplied by the consumer (title, address, phone...)
  • user_member_profiles: Information strictly handled by admins (registration fee compensated, conferences attended, etc.)

This can be condensed into one table, saving me head aches and keeping my code clean - however i seem like it's easier to leave them separate because they serve slightly different reasons.

Option 1: Let it rest by doing this and doing JOINs and tiresome UPDATEs (this bit of data would go to this table, this piece would go to another, etc.). More work with me, but maybe it will work better?

Option 2: Mix everything into one table.

I'd assume one table could be faster, you don't need to join tables. Maybe this is dependent around the data? Each table has about 12-20 fields, therefore the combined table could be large.

Each user has a maximum of 1 profile from each table, but might not actually have a profile whatsoever (or might have only one total).

To include just a little context for this: It's to have an ever-changing Content management systems designed in PHP that we will have to make tweaks towards the tables for every installation. Admins have to manage people inside a speadsheet-like way, so I'll be choosing as much as 200 customers at any given time.

What's the correct method of this from the performance, design, or organization perspective?

My design urges say keep separate because maybe later on a person may have two profiles, but performance is probably better if they're merged. If there's a classic one-to-one relationship, which relationship won't ever change, i quickly would merge them.

The look problem is whether or not you must have multiple records in almost any of individuals tables for just one user. If that's the case, don't mix them. When the tables have been in a 1-to-one realtionship, you are able to mix them, but shouldn't should they have many fields or perhaps your record size is going to be too wide which could cause performance problems in addition to which makes it impossible to include data should you exceed the particular record size limit for any single record. Should you presently have lots of code that access them as serarate tables and lots of data, restructuring them for that minor gain you can get (saving all one minute approximately in development and most likely virtually no time whatsoever inperformance towards the customers) appears to become a bad idea. You can write sights so it's not necessary to perform the joins, but honestly they are so simple, I would not bother there either.

Another point to consider with wide table (many posts) may be the impact on the RDBMS cache. Worthwhile developer knows you don't do 'select * from table' because it will carry unnecessary data within the network from RDBMS to client. But an identical effect sometimes happens betwen disk and RAM as well as affect the quantity of space in RAM that the table requires to cache.

Most RDBMSes allocate confirmed amount of memory to cache data, thus reducing physical disk reads and speeding reaction to the consumer. This really is Buffer Cache in Oracle or SQL Server

For those who have a large table and problem a question within the form 'select col1, col2, col3 from table' the RDBMS will load the entire rows into RAM (not col1 through 3). Because it achieves this it'll age out older cached data. In case your table is wide and also you load 50 posts you obviously require more RAM than for the similar quantity of rows * a narrow table. This may have a noticeable effect on RDBMS performance.

Plenty of wide tables, aging out other tables from cache and you'll be able to begin to see the IO stats go thorough the rooftop as generally used tables age from cache to create room for wide tables.

This factor should be included to another benefits of normalised data and taken into account at table design time. Essentially for those who have a potentially wide table with a few data that'll be regularly utilized plus some that'll be rarely, consider multiple tables having a 1-to-1 relationship.