Duplicated table for each website user

database-design

I have started working on a new database and found something that stands out as abnormal.

I have a table that holds contacts similar to below

contacts
+----+-------------+-------------+----------------------+
| id | first_name  | second_name |    email_address     |
+----+-------------+-------------+----------------------+
|  1 | jane        | Doe         | Jane.doe@example.com |
|  2 | John        | Doe         | john.doe@example.com |
+----+-------------+-------------+----------------------+

Each user has access to specific users on the website. The current way our system handles this is to duplicate the table and append the users id to the end of the table name, it then only inserts records relevant to the user.

contacts_201
+----+-------------+-------------+----------------------+
| id | first_name  | second_name |    email_address     |
+----+-------------+-------------+----------------------+
|  1 | jane        | Doe         | Jane.doe@example.com |
+----+-------------+-------------+----------------------+

I always thought in situations like this a link table would be used like this to resolve what is a many to many relationship.

contactMemLink
+------------+--------+
| contact_id | mem_id |
+------------+--------+
|          1 |    201 |
+------------+--------+

Extra information:

  • There are around 24000 records in the contact table
  • There are around 100 users on the website

  • Each user has the possibility of having every user assigned to it.

    My Question

Is there any reason on why the database would have been designed this way based on best practices?

Best Answer

IMHO

The original layout looks like it is a self-built implementation of Row Level Security (RLS). RLS allows the developer to limit the available rows based on who is logged in (and other pieces of information).

The reason for the original design could have been done because:

  1. The designer was unaware of RLS
  2. The RDBMS did not support RLS
  3. DBA Stack Exchange wasn't available to the developer at the time of implementation
  4. The developer copy+pasted code unaware of the potential problems
    • constructing the SQL statements for contacts_201 screams "Possible SQL Injection"

The solution for (2) would be based on these words of wisdom:

use the right tool for the job.

In other words, the RDBMS (that doesn't support RLS) should have been changed to an RDBS that supports the Business Requirement (ie one that does support RLS). This could be a simple as doing an upgrade.

note "Database Agnostic Code is a Myth" could have been the major reason to not fix (2).

Related Question