I have 3 MySQL tables for a membership system.
users: Minimum requirement to be a user, only related to account info (email, password, is_activated, etc)user_profiles: Personal information provided by the user (name, address, phone...)user_member_profiles: Information strictly managed by admins (registration fee paid, meetings attended, etc.)These could be condensed into one table, saving me headaches and keeping my code clean - but I feel like it's better to leave them separate as they serve slightly different purposes.
Option 1: Leave it this way and keep doing JOINs and tedious UPDATEs (this piece of data goes to this table, this piece goes to another, etc.). More work for me, but maybe it makes more sense?
Option 2: Combine everything into one table.
I would assume using one table would be faster, no need to join tables. Maybe this depends on the data? Each table has about 12-20 fields, so the combined table would be large.
Each user has no more than 1 profile from each table, but may not even have a profile at all (or may have only 1 total).
To add a little context to this: It is for an ever-evolving CMS written in PHP which I will need to make tweaks to the tables for each installation. Admins need to manage members in a speadsheet-like way, so I will be selecting up to 200 users at a time.
What is the correct approach to this from a performance, design, or organization perspective?
Another factor to consider with wide table (many columns) is the effect on the RDBMS cache. Any good developer knows that you don't do 'select * from table' as it will carry unnecessary data over the network from RDBMS to client. But a similar effect can happen betwen disk and RAM and also affect the amount of space in RAM that a table requires to cache.
Most RDBMSes allocate a given volume of memory to cache data, thus reducing physical disk reads and speeding response to the user. This is Buffer Cache in Oracle or SQL Server
If you have a wide table and issue a query in the form 'select col1, col2, col3 from table' the RDBMS will load the full rows into RAM (not col1 through 3). As it does so it will age out older cached data. If your table is wide and you load 50 columns you of course require more RAM than for the same number of rows * a narrow table. This can have a noticeable impact on RDBMS performance.
Lots of wide tables, aging out other tables from cache and it is possible to see the IO stats go thorough the roof as commonly used tables age out of cache to make room for wide tables.
This factor should be added to the other advantages of normalised data and taken into consideration at table design time. In effect if you have a potentially wide table with some data that will be regularly accessed and some that will be rarely, consider multiple tables with a 1 to 1 relationship.