How to store repeating rows efficiently in MySQL or any RDBMS?

Question

We have two Tables Users, UserMatches

Users (UserID, param1, param2....)
We store profile of users in Users table

UserMatches(UserId, matchedUserID)
For every users there could be 0..N matching users

If there are 100 matches for UserID there is repetition of

UserId, UserId1
UserId, UserId2
.....
UserId, UserId100

As you can see the UserMatches table grows NxM. Is there an efficient way we can store this information.

Please note that we cannot store all the matched users in comma separated text. We need a better structure so that we can easily update the table.

Answer 1

What you are using is the optimal structure.

Answer 2

Any time you have a many to may relationship, the way to store it is as pairs of record IDs.

Is your criteria for being a match reflexive? That is does UserID1 being a match for UserID2 mean that UserID2 is a match for UserID1?

If so , you can put in the restriction that UserID1 < UserID2 when you add records to the table to avoid duplication.

Answer 3

To actually save the possible Cross Join? Or do you mean you want a View table or Temp table with the stuff? You need to refer to the documentation how wholesale create ... select is done.

If you are talking about Exporting the matching relation XML is much preferred way over comma separated.

Answer 4

You're speaking of a many-to-many relationship and the only way to implement this is by a junction table like UserMatches.

The structure is the best. There is nothing to worry about.

Hope this helps.

Answer 5

Is the matching records unique to itself? If yes, I'm afraid your structure is the right one, otherwise you should normalize your database more.

Btw, why afraid that your table grows? It can handle millions or even billions of records without any hitch.