使用继承在Postgres中反模式（OTLT） [英] anti the anti-pattern (OTLT) in Postgres using Inheritance

问题描述

我知道一个真正的查询表的概念是一种反模式，通常不应该使用（请参阅网上的许多文章）。但是，我想知道在Postgres中使用表继承时是否仍然如此？

您永远不会读取或插入主查询表中-它更多地用作模板您的其他查找表，则不会丢失任何ref完整性，（可能会获得由于表中数据量较大而在缓存块中浪费的空间），并且在插入子表时，您不会甚至需要一个类型。

我的OTLT将具有所有查找表所需的以下列：

 创建表sl_lookupmaster 
（
 lookupid bigserial NOT NULL，
 parent bigint，
 tstamptdt不带时区的时间戳记NOT NULL DEFAULT localtimestamp，
描述字符变化（500）非空，
 entityref bigint非空，
删除布尔值非空默认值false，
约束sl_lookupmaster_pkey主键（lookupid）
）
  

然后

人们怎么认为，这仍然是设计错误，还是OTLT吗？

解决方案

关于数据库设计，有很多经验法则，很多人在不真正理解规则基本原理的情况下，坚持捍卫宗教战争的观点。 此处有一个很棒的线程一个人在哪里问为什么 OTLT如此邪恶？那里有十几个人说：天哪，太糟糕了！

最重要的是，如果表是相对静态的，那么没有太多用户点击它们同时，如果您可以控制谁/什么/如何将数据输入表中，并且从逻辑设计的角度来看，您仍在对单独的查找表进行建模，那么您可以将OTLT作为一种物理实现而放弃。我已经设计数据库25年了，在我看来，只要您了解该决定的含义，就应该随意违反规则。在设计任何东西时总是要权衡取舍。如果您睁开眼睛做出取舍，那么无论做出什么决定都应该是一个好的决定。

假设您对各种附带条件都可以，例如确保OTLT不会成为垃圾的热点或烂摊子，那么我认为您建议的物理实现似乎在确定和优雅之间。

I know the concept of "One true lookup table" is an anti-pattern and shouldn't usually be used (ref many many articles on net). However, I wondered if that still the case when you use table inheritance in Postgres?

You would never read or insert into master lookup table - it acts more as a template for your other lookup tables, you don't loose any ref integrity, (possibly you gain space that would otherwise be wasted in your caching blocks due to larger amount of data in tables), and as your inserting through the child tables, you don't even need a type.

My OTLT would have the following columns that are required for all lookup tables:

CREATE TABLE sl_lookupmaster
(
  lookupid bigserial NOT NULL,
  parent bigint,
  tstamptdt timestamp without time zone NOT NULL DEFAULT localtimestamp,
  description character varying(500) NOT NULL,
  entityref bigint NOT NULL,
  deleted boolean NOT NULL DEFAULT false,
  CONSTRAINT sl_lookupmaster_pkey PRIMARY KEY (lookupid)
)

Then you inherit off of that.

What do people think, is this still a design mistake, is this still OTLT?

解决方案

There are a lot of rules of thumb around database design and a lot of people get adamant to the point of "religious war" over defending them without really understanding the principles underlying the rule. There's a really awesome thread here where a guy is just asking why is OTLT so evil? There's a dozen people there saying "oh man, it's bad!" and one guy eventually giving a few realistic down-sides.

The bottom line is that if your tables are relatively static, if you don't have too many users hitting them at the same time, if you have controls over who/what/how data gets into the table and if from a logical design perspective you are still modelling separate lookup tables, then you can probably get away with OTLT as a physical implementation.

I've been designing databases for 25 years and in my opinion you should feel free to break a rule as long as you understand what the implications of that decision are going to be. There are always tradeoffs in designing anything. If you make your tradeoffs with your eyes open then whatever decision you come to should be a good one.

Assuming that you're OK on the various provisos like making sure your OTLT won't become a hotspot or a morass for junk then your proposed physical implementation seems somewhere between "OK" and "elegant" in my view.

这篇关于使用继承在Postgres中反模式（OTLT）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！