How to create a custom table for a dating site?

Question

I am working on the next version of a local dating site, PHP and MySQL, and I want to do everything right. The user table is quite massive and is expected to grow even more with the new version, as there will be a lot of money spent on promotion.

The current version, which I think is 7-8 years old, was probably made by someone who is not very good at PHP and MySQL, so I need to start from scratch.

Currently, the community has users of 200 thousand + and is expected to grow to 500 thousand -1 mil in the next one to two years. There are more than 100 attributes for each user profile, and I should be able to search for at least 30-40 of them.

As you can imagine, I'm a little wary of compiling a table with 200k rows and 100 columns. My predecessor split the user table into two ... one with the most frequently used and regular columns and one with the rest (and in bulk) of the columns. But this leads to big synchronization problems between the two tables.

So, what do you think is the best way to do this?

Answer 1

This is not an answer as such, but since several answers have suggested an attribute value model here, I just wanted to jump in and tell my life experience.

120 + ( 5-10 ) 100 ( 6 ), , user_id.

, ( , ), , user_id,attrib . , , . 3 130 . , value, , .

, OP, . , (, , 50% ) .

, OP, 30-40 , , 30-40 group_concat() - .

, , . , .

EDIT: . , ENUM().

2: , , ( ), . p >

Answer 2

, .

1) :

"" , ​​ , , , , role_id, registration_date ..

, , . = > val.

2) : user_profile

: user_id, ,

user_id: 1

: profile_image

:/uploads/12/myimage.png

user_id: 1

option: questions_answered

: 24

, , .

Answer 3

-- :

http://en.wikipedia.org/wiki/Entity-attribute-value_model

100 :

user_id, property, value.

Answer 4

.

, , 1 , , ab. , , 1 - mysql. , , , .

, , , , ( , mysql html), .. .

Answer 5

, , !

, .

2- . , .

, (.. , )....

. N- , , ( , PostgreSQL ). , , - :

SELECT candidate.id, 
 COUNT(*)
FROM users candidate,
  attributes candidate_attrs,
  attributes current_user_attrs
WHERE current_user_attrs.user_id=$current_user 
  AND candidate.user_id<>$current_user
  AND candidate.id=candidate_attrs.user_id
  AND candidate_attrs.attr_type=current_user.attr_type
  AND candidate_attrs.attr_value=current_user.attr_value
GROUP BY candidate.id
ORDER BY COUNT(*) DESC;

, . , :

SELECT candidate.id, 
 COUNT(*)
FROM users candidate,
   attributes candidate_attrs,
   attributes current_user_attrs
WHERE current_user_attrs.user_id=$current_user 
  AND candidate.user_id<>$current_user
  AND candidate.id=candidate_attrs.user_id
  AND candidate_attrs.attr_type=current_user.attr_type
  AND candidate_attrs.attr_value 
     BETWEEN current_user.attr_value+$tolerance
     AND current_user.attr_value-$tolerance
GROUP BY candidate.id
ORDER BY COUNT(*) DESC;

( $tolerance - attr_type, attr_value).

:

SELECT candidate.id, 
  SUM(1/1+
      ((candidate_attrs.attr_value - current_user.attr_value)
        *(candidate_attrs.attr_value - current_user.attr_value))
  ) as match_score
FROM users candidate,
  attributes candidate_attrs,
  attributes current_user_attrs
WHERE current_user_attrs.user_id=$current_user 
  AND candidate.user_id<>$current_user
  AND candidate.id=candidate_attrs.user_id
  AND candidate_attrs.attr_type=current_user.attr_type
  AND candidate_attrs.attr_value 
   BETWEEN current_user.attr_value+$tolerance
   AND current_user.attr_value-$tolerance
GROUP BY candidate.id
ORDER BY COUNT(*) DESC;

, ,

SELECT candidate.id, 
  SUM(1/1+
      ((candidate_attrs.attr_value - current_user.attr_value)
        *(candidate_attrs.attr_value - current_user.attr_value))
  ) as match_score
FROM users candidate,
  attributes candidate_attrs,
  attributes current_user_attrs,
  attribute_subsets s
WHERE current_user_attrs.user_id=$current_user 
  AND candidate.user_id<>$current_user
  AND candidate.id=candidate_attrs.user_id
  AND candidate_attrs.attr_type=current_user.attr_type
  AND candidate_attrs.attr_value
  AND s.subset_name=$required_subset
  AND s.attr_type=current_user.attr_type 
   BETWEEN current_user.attr_value+$tolerance
   AND current_user.attr_value-$tolerance
GROUP BY candidate.id
ORDER BY COUNT(*) DESC;

, (, , -). , , .

, PHP - .

- N- , , . - , , .

Answer 6

, . - Mysql 3NF BNCF. , 100 1 .

, INNODB.