Yet another database design dilemma

Question

We are working on a Cake PHP project with Mysql. Consider these three user types we have right now - Admin, Managers and Publishers (under managers).

Currently, an admin user can see a list of publishers in a dropdown and we need to have some more admin users and restrict the number of publishers viewable by them allowing every such admin to view a different combination of publishers. We have to first make a demo ready and then there could be more change requests. I would like to have your views on the database design, considering the current requirements and some more expected requirements.

Details
Publishers are stored in a table publishers having a field manager_id (the manager under whom a publisher is there). Every publisher has a record in the table. We have memcaching enabled and there is a memcache key to store the list of all publishers. Similarly we have memcache keys for managers list, hierarchical list of managers and publishers.

Now, we need to have some more admin users (currently there is only one) and restrict the number of publishers viewable by them. We should allow one restricted list for every such admin user, so there could be different combination of publishers visible for each case. Seems like there will be hardly 10-15 such users. This part has to be there in the demo, but in addition to that we would probably like to keep provision for easily restricting the managers list too. So, similar to publishers, we should be able to display a different combination of managers for every admin with restricted access. There are some modules where we display a hierarchical list of managers and publishers. It is not yet certain how to deal with that module. We have also not decided to have a UI for editing the restricted list for the concerned admin users. For the time being, we may not be having a UI for editing the restricted list for every admin, so it is important that the solution has to be such that it is easy to edit things manually in the back-end.

Note - We are going to deliver a demo for client. We may not be coming up with a UI for editing the restricted list for every admin right now, so the solution has to be such that it is easy to edit things manually in the backend.

We have thought of these approaches –

1. a. A new table with fields admin_id, manager_id, publisher_ids to keep the associations, and multiple records for every admin with restricted access – one record each for every manager that needs to appear in the restricted list. The publisher_id field will store all the publisher ids under that manager in csv format.

   b. A new table with fields admin_id, manager_ids, publisher_ids having a single record for every admin with restricted. The manager_ids field will store all the manager ids in csv format, which need to appear in the restricted list. All the publishers (under the managers) will be in csv format.

2. Instead of creating a separate table to store restriction specifications, adding a new field display_in_limited_list_of_admins to the existing tables for publishers and managers.This field will store a csv list of admin ids under which this publisher needs to appear.

I like the last approach. Note that there might me hardly 10-15 admin users with restricted permissions, but there are around thousands of managers, publishers and still increasing. So, we have to store the least amount of information in this case. Also, we do not have to do any more number of queries. We just add a where condition to the current queries for fetching the list of publishers etc. When the UI is ready, the updation logic looks easy, just make the display_in_limited_list_of_admins fields of the two tables (publishers and managers) true. Till then, we have to manually run these two queries, at max.

So, your opinions please... Anything I could have missed... when/why would somebody go with adding a new association table for this?

Answer 1

The integrity constraints for this kind of thing are a little tricky. I identified the tricky part. (Near the end.) Also, I omitted ON UPDATE CASCADE and ON DELETE CASCADE for readability and focus.

create table admins (
  admin_id integer primary key  -- references Persons (person_id), not shown
);

insert into admins values (1);
insert into admins values (2);
insert into admins values (3);

create table managers (
  manager_id integer primary key  -- references Persons (person_id), not shown
);

insert into managers values (100);
insert into managers values (101);
insert into managers values (102);

create table publishers (
  publisher_id integer primary key,  -- references Persons (person_id), not shown
  -- A manager can manage more than one publisher.
  manager_id integer not null references managers,
  -- Not redundant. You need this unique constraint for data integrity in the 
  -- table publisher_manager_admins below.  The primary key guarantees only 
  -- one row per publisher. (Per publisher id number, that is.)
  unique (publisher_id, manager_id)
);

insert into publishers values (200,100);
insert into publishers values (201,100);
insert into publishers values (202,101);


-- Controls which publishers an admin can see.
create table viewable_publishers (
  admin_id integer not null references admins (admin_id),
  publisher_id integer not null references publishers (publisher_id),
  primary key (admin_id, publisher_id)
);

insert into viewable_publishers values (1, 200);
insert into viewable_publishers values (1, 201);
insert into viewable_publishers values (1, 202);
insert into viewable_publishers values (2, 200);
insert into viewable_publishers values (2, 201);
insert into viewable_publishers values (3, 200);


-- Controls which managers an admin can see
create table viewable_managers (
  admin_id integer not null references admins (admin_id),
  manager_id integer not null references managers (manager_id),
  primary key (admin_id, manager_id)
);

insert into viewable_managers values (1, 100);
insert into viewable_managers values (1, 101);
insert into viewable_managers values (1, 102);
insert into viewable_managers values (2, 100);


-- Allows multiple admins per publisher. It's not clear whether this is actually
-- a requirement. If you're supposed to allow only one admin per publisher,
-- create a unique constraint on (admin_id, publisher_id).
create table publisher_manager_admins (
  admin_id integer not null,
  publisher_id integer not null,
  manager_id integer not null,

  -- Tricky part--involving overlapping, compound foreign keys--follows.

  -- Allow only existing publishers and their manager. 
  foreign key (publisher_id, manager_id) 
      references publishers (publisher_id, manager_id),
  -- Allow only the publishers this admin can view.
  foreign key (admin_id, publisher_id) 
      references viewable_publishers (admin_id, publisher_id),
  -- Allow only the managers this admin can view.
  foreign key (admin_id, manager_id) 
      references viewable_managers (admin_id, manager_id) 
);

insert into publisher_manager_admins values (1, 200, 100);
insert into publisher_manager_admins values (2, 200, 100);
insert into publisher_manager_admins values (2, 201, 100);

Inserts like these should fail.

-- The table "publishers" doesn't have the row (200, 102).
insert into publisher_manager_admins values (2, 200, 102);

-- The table "viewable_managers" doesn't have the row (3, 201).
insert into publisher_manager_admins values (3, 201, 100);

-- The table "viewable_publishers" doesn't have the row (2, 202).
insert into publisher_manager_admins values (2, 202, 101);

Answer 2

Maybe I miss someething, but it looks like a case of n:m relations between tables.

Should I model your domain, I'd make a relation table between 'admins' and 'managers' and another between 'managers' and 'publishers'.

admin     admin_manager       manager        manager_publisher      publisher
-----     -------------       --------       -----------------      ---------
id        admin_id            id             manager_id             id
          manager_id                         publisher_id

This way you have all the necessary to select and group as needed. For example, to select all publisher ordered by admin

SELECT *
FROM admin
INNER JOIN admin_manager ON admin.id = admin_manager.admin_id
INNER JOIN manager ON admin_manager.manager_id = manager.id
INNER JOIN manager_publisher ON manager.id = manager_publisher.manager_id
INNER JOIN publisher ON manager_publisher.publisher_id = publisher.id
ORDER BY admin.id ASC, manager.id ASC

If you want to select only publisher from admin with id 24 just add

WHERE admin.id = 24

if you want to count how may publisher has one manager:

SELECT manager.id,
       count(publisher.id) AS How_many_Publisher
FROM manager 
INNER JOIN manager_publisher ON manager.id = manager_publisher.manager_id
INNER JOIN publisher ON manager_publisher.publisher_id = publisher.id
GROUP BY manager.id
ORDER BY How_many_Publisher DESC

And so on.

All data is normalized and you can add as many admin, manager, publisher as you want, and each of them can relate with as many other you want. I.e. you can have a publisher with one manager, and this manager with two admins.