如何处理可选列
我的问题与 ServiceASpecificField
和 ServiceBSpecificField
相关。我觉得这两个字段放置不当,因为对于 SubscriberServiceMap
表中所有订阅者的 service A
的所有记录,ServiceBSpecificField
将具有 null 值,并且反之亦然。
如果我在订阅者表中移动这两个字段,那么我将遇到另一个问题。所有仅使用服务A
的订阅者将在Subscribers.ServiceBSpecificField
中具有空值。
那么理想情况下应该怎么做呢?
My question is related to ServiceASpecificField
and ServiceBSpecificField
. I feel that these two fields are placed inappropriately because for all records of service A
for all subscribers in SubscriberServiceMap
table, ServiceBSpecificField
will have null value and vice versa.
If I move these two fields in Subscribers table, then I will have another problem. All those subscribers who only avail service A
will have null value in Subscribers.ServiceBSpecificField
.
So what should be done ideally?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
在
Service_A 和 _B
表上放置检查约束,例如:然后你可以像这样加入
place check constraint on
Service_A and _B
tables like:then jou can join like
实现此目的的一个简单方法是问自己:这些列的值是否根据订阅(
SubscriberServiceMap
表)或服务而变化。 ?如果“服务 A”的每个订阅者的
ServiceASpecificField
值都相同,则您必须将其移至Services
表中。您预计有多少这样的领域? ServiceASpecificField、ServiceBSpecificField、C、D...等等?如果数量很大,您可以选择 EAV 模型,这将涉及创建另一个表。
An easy way to do this is to ask yourself: Do the values of these columns vary according to the Subscription (
SubscriberServiceMap
table) or the Service?If every subscriber of "Service A" has the same value for
ServiceASpecificField
, only then must you move this to theServices
table.How many such fields do you anticipate? ServiceASpecificField, ServiceBSpecificField, C, D... and so forth? If the number is sizable, you could go for an EAV model, which would involve the creation of another table.
这是一个简单的超类型-子类型问题,您可以在 5NF 中解决,为此您不需要 EAV 或改进的 EAV 或 6NF(完整且最终正确的 EAV)。由于 ServiceAColumn 的值取决于特定订阅者对服务的订阅,因此它必须位于关联表中。
▶标准化数据模型◀(内联链接在某些浏览器/版本上不起作用。)
不熟悉关系建模标准的读者可能会发现▶IDEF1X 表示法◀很有用。
这是一个普通的关系超类型-子类型结构。这是独占的:
Service
独占一个子类型。与其他答案相比,此模型中的关系和子类型更明确且更受控制。例如。 FK 关系特定于
Service
子类型,而不是Service
超类型。鉴别器是
ServiceType
,它标识任何超类型行属于哪个子类型。ServiceType
不需要在子类型中重复,我们通过子类型表知道它是哪个子类型。除非您拥有数百万个
服务
,否则短代码比无意义的数字更适合PK。其他
您可能会丢失
SubscriberService
中的Id
列,因为它 100% 冗余且没有任何用途。SubscriberService
的 PK 为(SubscriberId, ServiceId)
,除非您想要重复的行。请将列名称:
Subscriber.Id
更改为SubscriberId
;Service.Id
到ServiceId
。切勿使用Id
作为列名称。对于 PK 和 FK,请始终使用完整的列名称。当您开始编码时,您就会清楚其相关性。第六范式或 EAV
在添加具有新属性的新服务时添加列和表在关系数据库中是必要的,并且您可以保留很多控制和完整性。
如果您“不想”为每个新服务添加新表,那么可以使用 EAV 或 6NF,但请确保您拥有关系数据库中可用的正常控件(类型安全)以及数据和引用完整性。 EAV 通常在没有适当的关系控制和完整性的情况下实施,这会导致很多很多问题。这是问题/答案< /a> 关于这个主题。如果您确实同意这一点,并且该问题中的数据模型解释不够,请告诉我,我将为您提供一个特定于您的要求的数据模型(我上面提供的 DM 是纯粹的 5NF,因为这是您原始问题的完整要求)。
This is a simple supertype-subtype issue which you can solve at 5NF, you do not need EAV or improved EAV or 6NF (the full and final correct EAV) for this. Since the value of ServiceAColumn is dependent on the specific subscriber's subscription to the service, then it has to be in the Associative table.
▶Normalised Data Model◀ (inline links do not work on some browsers/versions.)
Readers who are not familiar with the Relational Modelling Standard may find ▶IDEF1X Notation◀ useful.
This is an ordinary Relational Supertype-Subtype structure. This one is Exclusive: a
Service
is exclusively one Subtype.The Relations and Subtypes are more explicit and more controlled in this model than in other answers. Eg. the FK Relations are specific to the
Service
Subtype, not theService
Supertype.The Discriminator, which identifies which Subtype any Supertype row is, is the
ServiceType
. TheServiceType
does not need to be repeated in the Subtypes, we known which subtype it is by the subtype table.Unless you have millions of
Services
, a short code is a more appropriate PK than a meaningless number.Other
You can lose the
Id
column inSubscriberService
because it is 100% redundant and serves no purpose.the PK for
SubscriberService
is(SubscriberId, ServiceId)
, unless you want duplicate rows.Please change the column names:
Subscriber.Id
toSubscriberId
;Service.Id
toServiceId
. Never useId
as a column name. For PKs and FKs, alway use the full column name. The relevance of that will become clear to you when you start coding.Sixth Normal Form or EAV
Adding columns and tables when adding new services which have new attributes, is well, necessary in a Relational database, and you retain a lot of control and integrity.
If you don't "want" to add new tables per new service then yes, go with EAV or 6NF, but make sure you have the normal controls (type safety) and Data and Referential Integrity available in Relational databases. EAV is often implemented without proper Relational controls and Integrity, which leads to many, many problems. Here is a question/answer on that subject. If you do go with that, and the Data Models in that question are not explanatory enough, let me know and I will give you a Data Model that is specific to your requirement (the DM I have provided above is pure 5NF because that is the full requirement for your original question).
如果 ServiceSpecificField 的值取决于服务和订阅者,并且对于所有订阅者服务对,该字段的类型是相同的(正如我在示例中看到的 - 两个字段都是 varchar(50) ),那么我将更新 SubscriberSerivceMap仅表:
此类表的示例:
If the value of ServiceSpecificField depends both on service and subscriber and for all subscriber-service pairs the type of the field - is the same (as I see in your example - varchar(50) for both fields), then I would update the SubscriberSerivceMap table only:
Example of such table: