主页 / dba / 问题 / 25291
Accepted
Phill Pafford
Asked: 2012-10-03 10:14:13 +0800 CST

一对多关系的查找表

  • 772

我有一个tickets表id，我需要将它关联到一个查找表，其中该数据的对应项是另一个id由外部源控制的数据。

tickets
- id
- sutff

lookup
- outside_data_id
- ticket_id

我只需要知道关联是否存在（因此是lookup表格）并且具有一对多的关系。outside_data_id我对除了它id本身和相关的东西一无所知ticket_id。

另外我不想改变tickets表格，因为这只会影响少量的ticket_id's.

例子

outside_data_id = 1234

可能与：

ticket_id = 321
ticket_id = 322
ticket_id = 900

下面的查找表结构会起作用还是有更好的方法？

CREATE TABLE lookup
(
   outside_data_id integer, 
   ticket_id integer
)
database-design postgresql

3 个回答

  1. Best Answer

    为此，您的表可以正常工作，但您可能想要添加索引。如果使用此表的主要原因是获取 outside_ticket_id 并获取相应的 ticket_id，我将添加以下聚集索引：

    CREATE CLUSTERED INDEX [CL_Lookup_OD_ID] on [lookup](outside_data_id)
GO

    如果主查找是相反的方式（尝试从 ticket_id 中查找 outside_data_id），则将聚集索引放在另一列上。

    ---- 哦，对不起，刚刚注意到这是 Postgres。上面的语法是 SQL Server。对于 Postgres，在列上创建索引，然后发出 cluster 命令，如下所示：

    CREATE INDEX IX_outside_data_id on lookup(outside_data_id);
CLUSTER lookup using IX_outside_data_id;

    您可能还想查看索引上的“填充因子”，具体取决于此处插入负载的重量。但这是一个值得自己探索的大话题……

    • 5

  2. 像您这样的结构可能应该通过以下方式解决：

    • m -table ( )上的多列主键约束和lookup
    • 引用1表的主键的外键约束。

    用于在一个方向上查找值的最佳索引由表的主键自动提供。lookup

    CREATE TABLE ticket (
   ticket_id integer PRIMARY KEY  -- possibly serial instead of integer
 , stuff text
);

CREATE TABLE lookup (
   outside_data_id integer
 , ticket_id integer REFERENCES ticket(ticket_id)
      -- ON UPDATE CASCADE  -- optional
      -- ON DELETE CASCADE  -- optional
 , CONSTRAINT lookup_pkey PRIMARY KEY (outside_data_id, ticket_id)
);

    用作ticket_id描述性名称。

    如果你 ..

    只需要知道关联是否存在

    ...那么您可能只需要一个普通的外键。 它会自动处理这一点，并提供额外的好处，即无论如何都会强制执行关系完整性。加上更多的选择。 它还坚持为每一个相关的项目，所以你可能无法使用它。
    filter_id

    所需的实际索引取决于您的工作量，多次阅读您的问题后我仍然不清楚。特别是，多列索引（或主键约束）中的列顺序是相关的 - 正如我们在这个相关问题下深入讨论的那样。

    为了获得最佳性能（忽略索引维护的成本，假设表没有更新太多）并且如果您的查询是双向的，您将在上面定义的主键之外创建另一个索引：

    CREATE INDEX lookup_reverse_idx(ticket_id, outside_data_id);

    虽然您可以在 上仅使用单列索引覆盖大多数其他用例ticket_id，但由于 PostgreSQL 存储中的数据对齐，索引中的两个整数列在磁盘上的大小与一个相同。因此，几乎没有任何额外收益的成本。

    在旁边

    使用不同 RDBMS 收集的经验并不总是适用于跨平台。

    PostgreSQL没有CLUSTERED INDEX类似的 SQL server。

    PostgreSQL 的CLUSTER命令关系松散，但工作方式不同。这是一次性操作，不会使表保持集群状态。它还用 a 的所有效果完全重写了表VACUUM FULL（在 Postgres 9.0 之后的现代版本中）。

    根据您的实际工作量CLUSTER，可能有用也可能没用。它可以用于查找多个相关行（outside_data_id-> ticket_id），尤其是对于没有大量更新的表。

    不过，建议FILLFACTOR是好的，特别是如果你使用CLUSTER- 如果你经常使用它实际上可能会损害性能UPDATE。

    • 5

  3. 您创建的结构看起来像是一个非常合理的查找表。

    CREATE TABLE lookup1
(
   outside_data_id integer NOT NULL 
   , ticket_id integer NOT NULL
);

    我可能会在此表中添加一个索引，例如：

    CREATE CLUSTERED INDEX IX_Lookup1 
    ON Lookup1 (outside_data_id, ticket_id) 
    WITH (ALLOW_PAGE_LOCKS=ON, ALLOW_ROW_LOCKS=ON, FILLFACTOR=100);

    （我的CREATE INDEX示例是针对 SQL Server 的，PostgreSQL 很可能需要进行一些更改！）

    • 3

相关问题