Geek Answers Handbook

Consolidate one-to-one tables

I have 3 MySQL tables for a membership system.

  • users: The minimum requirement for a user that is associated only with account information (email, password, is_activated, etc.).
  • user_profiles: personal information provided by the user (name, address, phone ...)
  • user_member_profiles: Information is strictly managed by administrators (registration fees, participation in meetings, etc.).

They can be compressed into one table, relieving me of headaches and keeping my code clean, but I feel that it is better to leave them separate, since they serve for several different purposes.

Option 1: Leave it this way and keep doing it JOINand tedious UPDATE(this part of the data goes on this table, this part goes on to the other, etc.). More work for me, but maybe that makes sense?

Option 2: Combine everything into one table.

I would suggest that using one table will be faster, there is no need to join tables. Maybe it depends on the data? Each table has about 12-20 fields, so the combined table will 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 common).

: CMS, PHP, . -, 200 .

, ?

+5
6

, ( ), - RDBMS. , "select * from table", RDBMS . , , .

, . Oracle SQL Server

" col1, col2, col3 ", RDBMS ( col1 3). , . 50 , , , , * . .

, , , IO , , , .

. , , , , 1 1.

+9

, , , , , , , , . - , , .

+2

.

VIEW, , , users user_profiles:

CREATE VIEW users2 AS
( SELECT u.id
       , u.email
       , u.password
       , u.is_activated
       , p.name
       , p.address
       , p.phone
  FROM users u
    LEFT JOIN user_profiles p
      ON u.id = p.id
)

, . VIEW ..

+2

, . , . , , , , , , . , , , ( , , ) , . , , , , , .

+2

, , , .

  • , ; ( ), .
  • (.. ), , , . , . , - .

, . , - . , , , .

+1

, .

, , , ( , ..). . , .

Such performance problems that everyone wants, and not many people do ...

-1
source

More articles:


All Articles