如何解决这种关系数据库设计的气味？ [英] How do I get around this relational database design smell?

问题描述

我有一个非常简单的mediaTypes表，其中包含以下列：

I have a really simple mediaTypes table which contains the following columns:

id string
name string

每个mediaType记录可以有很多放置，可以很容易地设计如下：

Each mediaType record can have many "placements", which could easily be designed as follows:

展示位置

id string
mediaTypeId string (links to mediaTypes.id)
name string
detail_col_1
detail_col_2
...etc

但是，根据媒体类型的不同，展示位置可以包含不同的详细信息，因此，如果以这种方式设计架构，则可能会导致很多可为空的列。

However depending on the media type, a placement can contain different details, so if I designed the schema this way I may end up with a lot of nullable columns.

要解决这个问题，我可以有一个aPlacements表和一个bPlacements表来匹配每种不同的媒体类型。

To get around this, I could have an aPlacements table and a bPlacements table to match each different media type.

aPlacements

  id string
  mediaTypeId string (links to mediaTypes.id)
  name string
  placement_details_relevant_to_media_type_col_1
  placement_details_relevant_to_media_type_col_2

bPlacements

  id string
  mediaTypeId string (links to mediaTypes.id)
  name string
  placement_details_relevant_to_media_type_col_1
  placement_details_relevant_to_media_type_col_2

此方法的缺点是我将如何通过id查找展示位置，因为我必须查询所有表：

The drawback of this is how would I then find a placement by id as I'd have to query across all tables:

SELECT * FROM aPlacements WHERE id = '1234'
UNION ALL
SELECT * FROM bPlacements WHERE id = '1234'
etc

整个设计感觉都有点设计异味。关于如何清理此架构的任何建议？

The whole design feels like a bit of a design smell. Any suggestions on how I could clean up this schema?

推荐答案

注意关系数据库标记。

整个设计感觉有点设计异味

The whole design feels like a bit of a design smell

是的。它闻起来有两个原因。

Yes. It smells for two reasons.

1. 每个表中都有 ids 作为标识符。这会使您感到困惑，并使代码容易搞砸。对于标识符：
   
   
   
   
   • 以它标识
     
     的名称将其命名。 mediaType ， placementCode （它们是字符串，是正确的）
   
   
   • 其中它作为外键定位，命名完全相同，因此不会混淆该列是什么以及它引用的PK
   
   

1. You have ids as Identifiers in each table. That will confuse you, and make for code that is easy to screw up. For an Identifier:
   • name it for the thing that it Identifies
     eg. mediaType, placementCode (they are strings, which is correct)
   • where it is located as a Foreign Key, name it exactly the same, so that there is no confusion about what the column is, and what PK it references

但是，根据 mediaType ，展示位置可以包含不同的详细信息

However depending on the mediaType, a placement can contain different details

1. 从逻辑上讲，您要寻找的是或门。
   
   在关系方面，它是子类型，这里是专有子类型。
   
   即具有完整完整性和约束。
   
   mediaType 是判别器。

1. What you are seeking in logical terms, is an OR Gate.
   In Relational terms, it is a Subtype, here an Exclusive Subtype.
   That is, with complete integrity and constraints.
   mediaType is the Discriminator.

如果我以此方式设计架构，我可能会遇到很多可为空的列。

if I designed the schema this way I may end up with a lot of nullable columns.

是的，您是正确的。可空列表示建模练习规范化不完整。两个子类型表是正确的。

Yes, you are correct. Nullable columns indicates that the modelling exercise, Normalisation, is incomplete. Two Subtype tables is correct.

• 我所有的数据模型都以 IDEF1X ，这是自1993年以来建立关系数据库模型的标准

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

我的 IDEF1X简介 对于初学者来说是必不可少的

My IDEF1X Introduction is essential reading for beginners

• 独家子类型

• 每个 Placement 是 PlacementA x或a PlacementB


• 请参考 子类型 ，以获取有关子类型实现的完整详细信息。

• Each Placement is either a PlacementA xor a PlacementB
• Refer to Subtype for full details on Subtype implementation.

关系键

• 它们是字符串，如您所给。


• 它们是关系模型所要求的由数据组成。


• 此类键是逻辑键，它们确保行是唯一的。


• 此外，它们还提供关系完整性（与参照完整性）（在此小型数据模型中无法显示）。


• 请注意，系统制造的 ID ，不是数据，用户看不到的是物理的，指向记录（不是逻辑行）。它们提供记录唯一性，但不提供行唯一性。它们不能提供关系完整性。


• RM 要求行（不是记录）是唯一的。

• They are strings, as you have given.
• They are "made up from the data", as required by the Relational Model.
• Such Keys are Logical, they ensure the rows are unique.
• Further they provide Relational Integrity (as distinct from Referential Integrity), which cannot be shown here, in this small data model.
• Note that IDs that are manufactured by the system, which is NOT data, and NOT seen by the user, are physical, pointing to Records (not logical rows). They provide record uniqueness but not row uniqueness. They cannot provide Relational integrity.
• The RM requires that rows (not records) are unique.

这样的缺点是然后，我将根据ID来查找展示位置，因为我必须在所有表中进行查询：

The drawback of this is how would I then find a placement by id as I'd have to query across all tables:

按上述方式进行升级， ：

Upgraded as per above, that would be:

这样做的缺点是我将如何通过PK Placement ，因为我必须在所有表中查询：

The drawback of this is how would I then find the relevant Placement columns by the PK Placement, as I'd have to query across all tables:

首先，请了解SQL非常适合关系数据库，但从本质上来说，它是一种低级语言。在现实世界中，我们大多数人都使用IDE（我不知道谁不使用IDE），因此，它的大部分麻烦都得到了缓解，并且消除了许多编码错误。

First, understand that SQL works perfectly for Relational databases, but it is, by its nature, a low-level language. Most of us in the real world use an IDE (I don't know anyone who does not), thus much of its cumbersomeness is eased, and many coding errors are eliminated.

我们必须直接编码SQL的地方，是的，这就是您要做的。习惯它。这里只有两个表。

Where we have to code SQL directly, yes, that is what you have to do. Get used to it. There are just two tables here.

您的代码将不起作用，它假定列是相同的数据类型且顺序相同（UNION必需）。

Your code will not work, it assumes the columns are identical datatypes and in the same order (which is required for the UNION). There are not.

• 不要强迫他们这样做，只是为了使UNION成功。稍后，一个或另一个子类型中可能还会有其他列，然后您的代码将在部署该代码的任何地方严重破坏。

• Do not force them to be, just to make your UNION succeed. There may well be additional columns in one or the other Subtype, later on, and then your code will break, badly, everywhere that it is deployed.

对于已实现的代码，请不要在SELECT中使用星号（仅适用于开发）。这样可以保证在数据库更改时发生故障。始终使用列列表，并仅请求您需要的列。

For code that is implemented, never use asterisk in a SELECT (it is fine for development only). That guarantees failure when the database changes. Always use a column list, and request only the columns you need.

SELECT Placement,
       ColumnA1,
       ColumnA2,
       ColumnB1 = "",
       ColumnB2 = "",
       ColumnB3 = ""  
    FROM  PlacementA  
    WHERE Placement = 'ABCD'  
--
UNION
--
SELECT Placement,
       "",
       "",
       ColumnB1,
       ColumnB2,
       ColumnB3  
    FROM  PlacementB  
    WHERE Placement = 'ABCD'

查看

关系模型及其SQL的数据子语言具有视图的概念。这就是使用它的方式。每个基本类型和子类型组合都被视为一个单元，单个行。

View

The Relational Model, and SQL its data sublanguage, has the concept of a View. This is how one would use it. Each Basetype and Subtype combination is considered a single unit, a single row.

CREATE VIEW PlacementA_V 
AS
    SELECT  Placement,
            MediaType,
            ColumnCommon,
            ColumnA1,
            ColumnA2
        FROM Placement  BASE
        JOIN PlacementA SUBA
            ON BASE.Placement = SUBA.Placement

享受。

在postgres中，有没有一种方法可以设置一个约束，使该布局只能存在于PlacementA或PlacementB中，而不能同时存在于两者中？

In postgres, is there a way I could setup a constraint where the placement can ONLY exist in either PlacementA OR PlacementB and not both?

1. 那是排他性。

1. That is Exclusivity.

• 如果您阅读链接的 Subtype doc，我已经给出了有关在SQL中实施的完整说明和技术细节 strong>，包括所有代码（遵循每个文档中的链接）。它由以下内容组成：
  
  。
  
  a CONSTRAINT 调用了功能
  
  。

• If you read the linked Subtype doc, I have given a full explanation and technical details for implementation in SQL, including all code (follow the links in each document). It consists of:
  .
  a CONSTRAINT that calls a FUNCTION
  .

ALTER TABLE ProductBook  -- subtype
ADD CONSTRAINT ProductBook_Excl_ck
 -- check an existential  condition, which calls
    -- function using PK & discriminator
 CHECK ( dbo.ValidateExclusive_fn ( ProductId, "B" ) = 1 )

根据我的经验，我们具有 SQL 功能超过15年。

Pusgre ** NON * sql在许多领域都不符合SQL。

所有免费软件/共享软件/蒸气软件/无软件都不符合SQL（它们对SQL的使用是欺诈性的）。他们没有服务器体系结构，大多数没有ACID事务等。

因此，没有。它不能从DDL调用函数。

Pusgre**NON*sql is not SQL compliant in many areas.
None of the freeware/shareware/vapourware/noware is SQL compliant (their use of the term SQL is fraudulent). They do not have a Server Architecture, most do not have ACID Transactions, etc.
Therefore, no. It cannot call a Function from DDL.

只要您了解并实现标准，例如 开放式架构 ，在您所能达到的程度 非 sql suite （由于它没有服务器体系结构，因此无法标记为平台），这是您可以做的最好的事情。

As long as you understand and implement Standards, such as Open Architecture, to the degree possible in your particular NONsql suite (it cannot be labelled a platform because it has no Server Architecture), that is the best you can do.

开放式架构标准要求：

• 没有直接的 INSERT / UPDATE / DELETE 到表



• 所有对数据库的写操作都是通过OLTP事务完成的。

• no direct INSERT/UPDATE/DELETE to the tables

• all your writes to the db are done via OLTP Transactions

• 在SQL中表示：
  
  具有 BEGIN TRAN ... COMMIT / ROLLBACK TRAN


• 但是在Pusgre中 NON sql的意思是：
  
  应该是原子的函数
  
  （之所以这样报价，是因为 SQL ACID交易 [ACID中的 A 代表原子）是没有实现的原子）

• which in SQL means:
  Stored Procedures with BEGIN TRAN ... COMMIT/ROLLBACK TRAN
• but in PusgreNONsql means:
  Functions which are supposed to be "atomic"
  (quotes because it is nowhere near the Atomic that is implemented in SQL ACID Transactions [the A in ACID stands for Atomic] )

因此，请使用我在 SQL中给出的功能中的排他性代码 ，并且：

Therefore, take the Exclusivity code in the Function I have given in SQL, and:

• 在 INSERT / DELETE 到您伪装的SQL套件中的Basetype或Subtype表。

（我不允许对键进行UPDATE，请参考 CASC上面的ADE 。）

• deploy it in every "atomic" Function that INSERT/DELETEs to the Basetype or Subtype tables in your pretend sql suite.
  (I do not allow UPDATE to a Key, refer CASCADE above.)

在这里，我们必须提到，这种原子函数同样需要具有以下代码确保基本类型-子类型对是否已作为INSERT / DELETEd对插入。

while we are here, it must be mentioned, such "atomic" Functions need to likewise have code to ensure that the Basetype-Subtype pair is INSERT/DELETEd as pair or not at all.

这篇关于如何解决这种关系数据库设计的气味？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！