SQL 中的多对多层次结构关系

Question

我正在尝试弄清楚如何在数据库中存储分层数据，但是我遇到了麻烦，因为数据似乎不适合简单的树层次结构。我正在研究市场指数和子指数，它们可以以多种不同的方式划分为多个不同的级别。我将尝试用一个简单的例子来解释。

假设我们有一个代表整个世界的指数(世界)，并且只有 2 个国家(美国和中国)。此外，股票只能分为两种类型(技术和医疗保健)。这将给我留下以下 9 个索引：

|-------------------|---|
|Index              |ID |
|-------------------|---|
| World             | 1 |
| USA               | 2 |
| China             | 3 |
| Technology        | 4 |
| Health Care       | 5 |
| USA Technology    | 6 |
| USA Health Care   | 7 |
| China Technology  | 8 |
| China Health Care | 9 |
|-------------------|---|

世界指数可以分为美国和中国，但也可以分为技术和医疗保健。此外，美国指数可分为美国科技指数和美国医疗保健指数，而美国医疗保健指数也是医疗保健的组成部分(与中国医疗保健一起)。

我希望能够检索对索引进行分组的各种不同方式。以下是一些示例：

• 按国家分组： { 1： [2， 3] }
• 按扇区分组： { 1： [4， 5] }
• 按国家、部门分组： { 1： [2： [6， 7]， 3： [8， 9]] }
• 按部门分组， 国家： { 1： [4： [6， 8]， 5： [7， 9]] }

关于如何在关系表中表示的任何建议？

Answer 1

如果您只想存储和检查索引/子索引、国家/地区和部门关系，那么一个解决方案涉及五个表：索引表、部门表、部门索引连接表、国家/地区表和国家/地区索引连接表。 索引表可以保存对任何父索引的引用(自联接)。 此架构显然对基数做出了一些需要确认的假设。 SQLfiddle

create table indices (indexid int not null primary key, name nvarchar(100), parentIndex int null,
foreign key (parentindex) references indices(indexid));
create table sectors (sectorid int not null primary key, name nvarchar(200));
create table countries (countryid int not null primary key, name nvarchar(200));
create table indices_join_sectors (sectorid int not null, indexid int not null,
foreign key (sectorid) references sectors(sectorid),
foreign key (indexid) references indices(indexid));
create table indices_join_countries (countryid int not null, indexid int not null,
foreign key (countryid) references countries(countryid),
foreign key (indexid) references indices(indexid));