Shared tenant objects

Question

I have a multi tenant application with a single database. I've a "entity" table where all objects are stored. "sahred_entity" table is used to store objects that are shared by a Tenant X to Tenant Y. For example "Tenant 2" can share "Entity with ID 4" to "Tenant 1".

In the example below "Entity with ID 4" is shared to "Tenant 1" and "Tenant 3"

+--------+--------------------------------------------------
| Table  | Create Table
+--------+--------------------------------------------------
| entity | CREATE TABLE `entity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int(10) unsigned NOT NULL,
  `added_at` timestamp NOT NULL,
  `color` varchar(20) NOT NULL,
  `size` varchar(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------

+---------------+---------------------------------------
| Table         | Create Table
+---------------+---------------------------------------
| shared_entity | CREATE TABLE `shared_entity` (
  `tenant_to` int(10) unsigned NOT NULL,
  `tenant_from` int(10) unsigned NOT NULL,
  `entity_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+---------------------------------------

The sample data is

select * from entity;

+----+-----------+---------------------+--------+------+
| id | tenant_id | added_at            | color  | size |
+----+-----------+---------------------+--------+------+
|  1 |         1 | 2019-03-07 00:00:00 | red    | m    |
|  2 |         1 | 2019-03-07 00:00:00 | green  | xl   |
|  3 |         2 | 2019-03-07 00:00:00 | green  | xl   |
|  4 |         2 | 2019-03-07 00:00:00 | red    | m    |
|  5 |         3 | 2019-03-07 00:00:00 | yellow | l    |
+----+-----------+---------------------+--------+------+

select * from shared_entity;

+-----------+-------------+-----------+
| tenant_to | tenant_from | entity_id |
+-----------+-------------+-----------+
|         1 |           2 |         4 |
|         3 |           2 |         4 |
+-----------+-------------+-----------+ 

Now I need to create a simple search query. For now I found two ways how to do it. The first is via self joining

SELECT e.* FROM `entity` as e
LEFT JOIN entity as e1 ON (e.id = e1.id AND e1.tenant_id = 1)
LEFT JOIN entity as e2 ON (e.id = e2.id AND e2.id IN (4))
WHERE (e1.id IS NOT NULL OR e2.id IS NOT NULL) AND e.`color` = 'red';

The second is via sub query and union

SELECT * FROM 
(
    SELECT * FROM entity as e1 WHERE e1.tenant_id = 1
        UNION
    SELECT * FROM entity as e2 WHERE e2.id IN(4)
) as entity
WHERE color = 'red';

Both of queries return expected result

+----+-----------+---------------------+-------+------+
| id | tenant_id | added_at            | color | size |
+----+-----------+---------------------+-------+------+
|  1 |         1 | 2019-03-07 00:00:00 | red   | m    |
|  4 |         2 | 2019-03-07 00:00:00 | red   | m    |
+----+-----------+---------------------+-------+------+

But which approach is better for large tables? How to create right index? Or maybe there is a better solution?

Answer 1

You could also use the following query to get the same results

SELECT *
FROM entity
WHERE (tenant_id = 1 or id = 4) AND color = 'red'

It is not clear to me why you need all the joins