1

Entities:

  • Users
  • Hotels
  • Roles

Relations:

When Users are assigned to a Hotel they are also assigned one or more Roles.

Example:

Alice (USER) can be assigned to Blue Moon Hotel (HOTEL) as Head Manager (ROLE). And also can be assigned to Rainbow Hotel (HOTEL) as a Marketing Manager (ROLE) AND a Reservation Manager (ROLE).

Problem

Hotels and Users tables will the biggest ones in the DB and they will be joined very frequently. We fear that the join operations will be too costly if we insert 2 rows for Alice and Rainbow Hotel (one for marketing manager and one for reservation manager). Considering the complexity is U^2 x H^2 x R^2 we might face some problems in the long run.

Current Approach:

Approach

Community
  • 1
  • 1
J. Nicastro
  • 254
  • 3
  • 11
  • What is your approach ? I can't see images. – Cid Apr 12 '19 at 14:25
  • @Cid Have a relation table with userId, HotelId and RoleId (having multiple rows if the user has 2 roles for the same hotel) – J. Nicastro Apr 12 '19 at 14:27
  • What's wrong with it ? – Cid Apr 12 '19 at 14:27
  • You certainly need to create the right indexes to make queries efficient. To get advice on query optimization, you should show the output of `SHOW CREATE TABLE ` for each table, and also show the SQL query you want to optimize. – Bill Karwin Apr 12 '19 at 14:27
  • @Cid In the long run we may face problems with the complexity of the query joining the 3 tables as hotels and users will be the tables with the biggest quantity of rows. – J. Nicastro Apr 12 '19 at 14:29
  • *"Considering the complexity is U^2 x H^2 x R^2"*: could you explain how you arrive to that? – trincot Apr 12 '19 at 14:30
  • You want indexes on your search criterias. This will increases the disk cost, but will greatly decrease the query execution time. – Cid Apr 12 '19 at 14:31
  • @trincot Worst case: every user is assigned to every hotel with every Role. U x H x R x UHR = U^2 x H^2 x R^2 – J. Nicastro Apr 12 '19 at 14:31
  • Will look into it @Cid – J. Nicastro Apr 12 '19 at 14:31
  • I also considered having a String field instead of roleId. having all ids separated by a comma on that field. So we have only one row per USER-HOTEL. We could get the Roles on another query after that. – J. Nicastro Apr 12 '19 at 14:33
  • Indexed fields use binary searches. it takes log2(n) checks before finding out that the requested search criteria doesn't match. For a 7 billions entries tables, this is 33 checks. – Cid Apr 12 '19 at 14:33
  • 4
    *"having all ids separated by a comma on that field"* This is a **very bad idea** – Cid Apr 12 '19 at 14:33
  • @Cid So there won't be any problem with my original approach? – J. Nicastro Apr 12 '19 at 14:34
  • 2
    You can use composite indexes instead. See [this](https://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index) – Cid Apr 12 '19 at 14:35
  • @J.Nicastro no problem, as long as you have enough disk space – Cid Apr 12 '19 at 14:36
  • It will never be U x H x R x UHR. Even if you select all related records, then you first select all records from the UHR-table and then with the indexes on primary keys of the base tables you just get the corresponding (unique) records from the U, H and R tables. So that is UHR + UHR. – trincot Apr 12 '19 at 14:36
  • @Cid that's exactly what I needed! Composite Indexes will do. Post it as an Answer if you want! Thanks a lot. – J. Nicastro Apr 12 '19 at 14:38
  • I don't want to plagiate an answer – Cid Apr 12 '19 at 14:41
  • @Cid Done. Thanks for your time! – J. Nicastro Apr 12 '19 at 14:43

0 Answers0