Question

我们正在与Mysql合作开展一项面包房项目。我们现在审议这三种用户类型——阿迪米特、经理和出版商(经理)。

目前,一个行政管理用户可以看到一份发行人名单的下降,我们需要拥有更多的行政管理用户,并限制出版商数量,从而使每个这类机构都能看到不同的出版商组合。我们必须首先做好准备,然后提出更多的更改要求。考虑到目前的需求和一些预期需要,我谨就数据库设计提出意见。

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.

现在,我们需要拥有更多的行政管理用户(目前只有用户),并限制他们认为的出版商数量。我们应该允许每个此类行政用户有一个限制名单,因此,每种情况下的出版商可以有不同的组合。这样的用户几乎只有10-15个。这部分内容必须放在议事日程上,但除了此以外,我们可能希望随时限制管理人员名单。因此,与出版商一样,我们应能够展示每个行政主管的不同组合,限制出入。有一些模块,显示管理人员和出版商的等级清单。处理这一模块还不确定。我们还没有决定为有关行政管理用户编辑限制名单设立统一标准。目前,我们可能还没有一个协调小组来编辑每个行政机构的限制性名单,因此,解决办法必须易于人工操作。

<>说明> 我们将为客户提供mo。我们现在可能不会收到一个联合倡议,以编辑每个行政权的限制性清单,因此解决办法必须易于人工操作背后的东西。

www.un.org/Depts/DGACM/index_spanish.htm 我们考虑了这些办法——。

a 。附有以下领域的新表格:admin_id,manager_id,publisher_ids,以保持这些协会,每个行政单位的多份记录,限制出入——每个管理人员都有一份记录,需要列入限制名单。 <代码>publisher_id 外地将储存该经理下的所有出版商。

b) 载有各领域的新表格:admin_id,manager_ids/code>,publisher_ids,每个有限制的行政机构都有单一记录。经理/女佣将储存所有经理的婴儿,需要列入限制名单。所有出版商(经理)将采用剪辑格式。

在现有的出版商和管理人员表格中添加一个新的外地<代码> 显示_in_limited_list_of_admin。这个领域将储存一份需要该出版商在场的商用咖啡清单。

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.

因此,请...... 没有人会错过......有人会为此增加新的联系表?

Answer 1

也许我错过一些思潮,但我看上去的是各表之间的微小关系。

如果我模仿你的领域,我就在管理人员和出版商之间提供一个关系表。

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

如果你只想从行政中挑选出24名出版商,仅增加24名出版商。

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

因此。

所有数据都是正常的,你可以像你所希望的那样,增加尽可能多的行政、管理人员、出版商,而且每个数据都可以与你们想要的其他许多数据联系起来。 I.e. 您可以拥有一名经理的出版商,该经理拥有两家行政。

Answer 2

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);

友情链接