是否有标准化形式来表示不同表中的多个对象？

Question

对于我当前正在构建的系统，会发生以下两种情况：

1. 我的权限系统长期以来一直最喜欢将权限附加到角色，将角色附加到用户，但有一个不同之处：权限可以应用于任何层的任何资产，并且有 4 个“层”，编号为 0 到 3。因此，角色分配表有 5 个字段长：资产层、资产 ID、角色 ID、用户 ID 和“禁用”标志。此“Tier/ID”系统还用于将用户分配到“家庭资产”。

   这个系统不幸的副作用是，在应用程序本身中解析这些资产引用通常要简单得多，而不是在 SQL 中（我知道这是可能的，但它会导致一些严重的问题）丑陋的查询）。 有没有更好的方式来表示这种关系？这是表示这种关系的可接受的方式吗？

   旁注：资产本身的表（它们是分层的）已标准化，外键引用从子级指向父级（例如，第 3 层包含有关第 2 层的信息等），因此一旦固定单个资产找到它的子级和祖先非常简单。

2. 类似的问题，但不同的数据上下文：在最低层，设备可以“附加”。还允许将设备放置在图形表示上（如地图），并且它们的排列信息存储在数据库中。图形引用（也称为“点”）应指向真实设备，但真实设备可能不会指向图形引用。

   目前，所有“点”（代表设备）都有一个参考表，其中包含其显示布局、位置、大小等信息。我可以从这里确定三个可能要做的事情：

   1. 创建一系列标准化表来表示各个设备，并使用类似的“设备类型/设备 ID”系统来查找它们，并将这些信息存储在“点”表中；这仍然需要应用程序执行中间工作来解析各自表中对实际设备的引用
   2. 创建一系列标准化表，并将它们指向“点”表；此解决方案可能不需要应用程序取消引用点数据，但需要扫描所有设备表以查找任何第 3 层对象。
   3. 使用积分表作为指导，将所有相关显示数据添加到每个相应的标准化表中。虽然这根本不需要解决任何查找问题，但它仍然需要我扫描每个设备表，并且与其他解决方案相比，也极不能容忍点数据存储方式的任何更改。

   这个问题已经解决了吗，只是我没有收到备忘录？是否有解决此问题的标准设计，或者我应该假设我必须自己解决这些引用？

注意：关于问题二，我认为以下两个解决方案很糟糕，我不会考虑它们（除非有一些疯狂的、令人惊奇的证据表明这是最好的方法，但我相当确定它不是）：

1. 将有关每个设备的所有信息（在序列化数组或某种此类机制中）存储在点表本身中。 这有一个明显的问题，即完全不可搜索并且违反正常形式
2. 将所有设备存储在单个通用表中。 不幸的是，这些设备在提供的数据类型以及访问方式方面都截然不同，并且在一个表中这样做需要（上次我尝试在纸上计算出来）至少 30 （可能还有更多）列，其中每行的一半以上将为空。

请求并赞赏任何解决方案，尽管我专门寻找解决此问题的现有模式和/或规范化模式（如果有）存在。如果您确实知道这是正确的答案，那么“否”就足够了，我将继续使用应用程序层来解析这些引用。这还不是一个问题，我只是想知道当有人已经解决了这个/这些问题时我没有在错误的方向上开辟一条道路。提前致谢。

编辑：权限/资产架构

第 0 层实际上是隐含的，而不是在数据库中，但有些东西被注册为第 0 层（资产 ID 0）

第 1 层：

id int(5) unsigned not null primary key auto_increment,
name varchar(32) not null,
disabled tinyint(1) unsigned not null,

第 2 层：

id int(5) unsigned not null primary key auto_increment,
parentId int(5) unsigned not null,
name varchar(32) not null,
abbr varchar(16) not null,
disabled tinyint(1) unsigned not null,
foreign key (parentId) references tier1(id)

第 3 层：

id int(5) unsigned not null primary key auto_increment,
parentId int(5) unsigned not null,
name varchar(32) not null,
abbr varchar(16) not null,
disabled tinyint(1) unsigned not null,
foreign key (parentId) references tier2(id)

权限

id int(5) unsigned not null primary key auto_increment,
permission_key varchar(16) not null,
permission_desc varchar(128) not null

：角色：

id int(5) unsigned not null primary key auto_increment,
name varchar(32) not null,
tier1_id int(5) unsigned not null,
disabled tinyint(1) unsigned not null,
foreign key (tier1_id) references tier1(id)

Role_Permissions：

role_id int(5) unsigned not null,
permission_id int(5) unsigned not null,
disabled tinyint(1) unsigned not null,
primary key (role_id, permission_id),
foreign key (role_id) references roles(id),
foreign key (permission_id) references permissions(id)

User_Role_Permissions：

tier_id tinyint(1) unsigned not null,
asset_id int(5) unsigned not null,
user_id int(5) unsigned not null,
role_id int(5) unsigned not null,
disabled tinyint(1) unsigned not null,
primary key (tier_id, asset_id, user_id, role_id),
foreign key (user_id) references users(id),
foreign key (role_id) references roles(id)

Answer 1

您是否考虑过将权限表分解为一组表，每个表适用于单独的对象表，例如 tier_1_assets 和 tier_1_permissions 以及 tier_2_assets 和 tier_2_permissions。这将使查询某一层资产的权限变得更简单，但查询用户的所有权限会变得更复杂（这是一种权衡）。

我还会质疑关系数据库是适合您的数据的存储介质的假设。确实可能是，但也可能不是。 （PHP 可能会限制这方面的自由）。

如果您的数据集很小，为什么不将其保存到单个序列化文件中，并拥有一个小型服务器将其保存在内存中并提供查询接口？我相信有充分的理由不这么做。 （除了 PHP 之外）

您是否考虑过使用多个点表来解决第二个问题？它可能感觉像是重复，但它确实使某些类型的查找变得更简单，并且当您将每个外键引用视为不同类型的对象时，它会被标准化（当表被修改时，您可以对它们使用外键检查）分开）。

对象之间关系的多样性应该告诉您将键和引用放在哪里：一对多 -> （键 <- 外键）；多对多-> (key <- 链接表 -> key);一对一可选-> （键 <- 外键）。

我不知道有任何模式符合您所说的问题。但我注意到，大多数避免创建另一个表的通用解决方案都很难快速管理。