[FIXED] How to store unique list with properties in MySQL

Issue

I want to store data like the following, unique is on user_id and lids, in MySQL:

recordid     user_id      lids       length     breadth
------------------------------------------------------------
1            1           l1,l2       10          5
2            1           l1          7           5
3            1           l1,l3,l2    10          10
4            1           l2,l3       25          15

My query patterns are:

• Give me length & breadth where lids are l2,l1
• Give me length & breadth where lids are l2,l3

Basically, the input of lids can come in any order to search, still it should provide the correct length, breadth.

Since, we should not store the comma separated values in RDBMS.

Question - How should I structure the DB to have unqiue user_id/lids combinations which can provide the correct length & breadth without much string operations?

I came up with a solution to query the DB like this -

select * from table1 where find_in_set('l2', lids) AND find_in_set('l1', lids);

then in code, identify the count to be exact 2 of lids. But it is not the perfect solution. Need guidance regarding it.

AddOn - A SpringBoot + JPA (Hibernate) specific solution will be great, where there is no requirement of writing native sql query

---

As per comments if I create a table for lids -

recordid(fk)    lid
----------------------------------
1               l1
1               l2
2               l1
3               l1
3               l3
3               l2
4               l2
4               l3

Then how will I ensure that just 1 unique combination of lids should be available for the user? and what will be my select query? Will it be like the following?

select * from table1, lids where main.recordid = lids.recordid and lid IN ('l2','l3');

The IN operator will run a OR query instead of AND which will give wrong results as well.

Do I have to group based on the recordid in lids table then apply where condition? Apologies, I'm totally confused as I have read many articles related to it and got distracted.

---

Okay the question basically drills down to this - How to find if a list/set is exactly within another list

I want to find recordid having EXACT list of lids to search.

Solution

tl;dr: for design problems like this think about entities. relationships, amd sets.

You have two entities, records and lids. They have a many-to-many relationship.

Let's call your second table records_lids, to show that it's a many-to-many association table between records and lids. It has two columns, record_id and lid. When a row exists in that table it means that the record_id mentioned has the lid mentioned.

That table's primary key should be made of both its columns (record_id, lid). Because primary keys are unique, this prevents any record from having the same lid more than once.

Now, finding the set of record_id values with lid l1 is easy. You don't even need your first table.

        SELECT record_id FROM records_lids WHERE lid = `l1` 

To find records with multiple lids, you need to take the logical intersection of the sets of records with each lid. You can do that like this: (https://www.db-fiddle.com/f/cLf4b6LDwMH9eFRTTheZJr/0)

SELECT record_id 
          FROM (SELECT record_id FROM records_lids WHERE lid = 'l1') l1
  NATURAL JOIN (SELECT record_id FROM records_lids WHERE lid = 'l2') l2
  NATURAL JOIN (SELECT record_id FROM records_lids WHERE lid = 'l3') l3 
 

The NATURAL JOIN operations handle the intersection operation; the result only includes rows with matching record_id values. (Some other makes of SQL table server have the INTERSECT operator, but not MySQL, yet...)

You can also do it this way (https://www.db-fiddle.com/f/cLf4b6LDwMH9eFRTTheZJr/1).

SELECT record_id
  FROM records_lids
 WHERE lid IN ('l1','l2','l3')
 GROUP BY record_id
 HAVING COUNT(*) = 3

The HAVING clause is how you insist you want records with all three lids.

Once you have the set of record_ids, you can join that to your other table. (https://www.db-fiddle.com/f/cLf4b6LDwMH9eFRTTheZJr/2)

SELECT records.* 
          FROM (SELECT record_id FROM records_lids WHERE lid = 'l1') l1
  NATURAL JOIN (SELECT record_id FROM records_lids WHERE lid = 'l2') l2
  NATURAL JOIN (SELECT record_id FROM records_lids WHERE lid = 'l3') l3 
  NATURAL JOIN records

or (https://www.db-fiddle.com/f/cLf4b6LDwMH9eFRTTheZJr/3)

SELECT * 
  FROM records
  WHERE record_id IN (
    SELECT record_id
      FROM records_lids
     WHERE lid IN ('l1','l2','l3')
     GROUP BY record_id
     HAVING COUNT(*) = 3
)

Edit: I did not completely understand your question. You want to exclude records without an *exactly( matching set of lids. Try this (https://www.db-fiddle.com/f/cLf4b6LDwMH9eFRTTheZJr/4). It depends on a quirk of MySQL, which is that Boolean expressions like lid IN ('l1', 'l2') have the value 0 when false and 1 when true.

SELECT * 
  FROM records
  WHERE record_id IN (
       SELECT record_id
         FROM records_lids
     GROUP BY record_id
       HAVING SUM(lid IN ('l1', 'l2')) = 2
          AND COUNT(*) = 2 
)

SQL is, at its heart, a language for manipulating sets. The design technique here is

1. figure out your entities
2. work out the relationships between them
3. work out how to get the sets of entities you require
4. retrieve the rows you need matching the sets

Answered By - O. Jones