建模/约束问题 [英] Modeling/Constraint question
问题描述
我将尽力尽力描述这种情况，但如果
任何事情都不清楚，请告诉我。在我到达现场之前，这个数据库中的一些已经到位了，而且我并不是真的
有买入来改变它。
有一个人可以持有的证书表。一个人可以
持有各种类型的证书。我需要进一步将一个人的
证书组合在一起，以便（例如）更新日期可以是
同步。只有某些类型的证书可以链接在一起，这是在另一组表中定义的。这是模型的首次亮相
。我从表格中删除了一些
中不相关的列。
- 这个表已经存在了原样
CREATE TABLE dbo.Person（
ID INT NOT NOT IDENTITY，
FirstName VARCHAR（30）NULL，
LastName VARCHAR（30）NULL）
GO
ALTER TABLE dbo.Person
ADD CONSTRAINT PK_Person PRIMARY KEY（ID）
GO
- 此表已经按原样存在
CREATE TABLE dbo.Certificate（
ID INT NOT NULL IDENTITY，
PersonID INT NOT NULL，
CertificateTypeID INT NOT NULL） - 这个FK的表是
只是一个ID和描述。此处不包括简洁原因
GO
ALTER TABLE dbo.Certificate
ADD CONSTRAINT PK_Certificate PRIMARY KEY（ID）
GO
ALTER TABLE dbo.Certificate
ADD CONSTRAINT FK_CertificatePerson FOREIGN KEY（PersonID）REFERENCES
dbo.Person（ID）
GO
- 这张表是新的，代表哪些类型的证书可以
链接和同步
CREATE TABLE dbo.CCRTypeCertificateType（
CCRTypeID INT NOT NULL， - 这个FK到一个表是
，主要只是一个ID和描述
CertificateTypeID INT NOT NULL）
GO
ALTER TABLE dbo.CCRTypeCertificateType
ADD CONSTRAINT PK_CCRTypeCertificateType PRIMARY KEY（CCRTypeID，
CertificateTypeID）
GO
- 此表定义了哪些实际证书链接在一起
CREATE TABLE dbo.CCRCertificate（
CCRT ypeID INT NOT NULL，
PersonID INT NOT NULL，
PrimaryCertificationID INT NOT NULL， - 这是必要的
因为一个人可以让一个人认证失效，然后开始新的
与旧款相同类型之一
认证ID INT NOT NULL）
GO
ALTER TABLE dbo.CCRCertificate
ADD CONSTRAINT PK_CCRCertificate PRIMARY KEY（CCRTypeID，PersonID，
PrimaryCertificationID，CertificationID）
GO
其他表格挂在CCRCertificate表格之外。同一个表中的PersonID和
CertificationID似乎违反了Boyce-Codd Normal
表格，所以我被要求删除PersonID（不是说BCNF是
当然给出了理由，但它是重复数据，因为
PrimaryCertificationID应该已经确定了PersonID）。
我想做的是：
1.限制CCRCertificate表（如果需要
，则使用附加表）因此，作为CertificationID链接的所有认证都是来自同一个人的
。
2.要求每种类型的证书最多为
CCRType的CCRTypeCertificateType被添加到CCRCertificate。在
中，如果CCRTypeCertificateType表指示
CCRType 1由CertificationType 1和CertificationType3组成，则
CCRCertificate表应该如果
CCRTypeID为1，则只包含最多一个认证
，其认证标识为1，另一个认证标识为3。
我希望这不会太混乱。让我知道是否有什么东西
我可以更清楚。
谢谢！
-Tom。
PS这里的SQL用于说明目的。我在笔记本电脑上没有安装SQL Server，因此我无法在
时刻测试SQL。因为我不是要求任何可能需要测试的代码
我希望这不是问题。
我发现有点奇怪的是，数据库在CCRCert表中具有PrimaryCertificationID
和CertificationID作为foreignkeys。我不会在这里假装理解业务方面，但如果证书可以是其他证书的主要证书，那么应该有一个关系
证书表中的
行，而证书涉及到
本身。这个模型并不是特别无效，但是当你开始
在该表中添加其他外键时，
更难以保持完整性，因为它看起来你正在体验。
你提到你没有购买改变设计，但在证书中可能有一个可能的关系它的主要证书可能是
让事情变得更容易???
希望有所帮助。
5月22日下午4：46 *，Eric Isaacs< eisa ... @ gmail.comwrote：
我是什么发现有点奇怪的是数据库在CCRCert表中有PrimaryCertificationID
和CertificationID作为foreignkeys。 *我不会在这里假装理解业务方面，但如果证书可以是其他证书的主要证书，那么应该有一个关系。 />
代替证书表中的行，其中证书与
本身有关。 *这个模型并不是特别无效，但是当你开始使用
在该表中添加其他外键时，
更难以保持完整性，因为它看起来就像你正在经历的那样。
你提到你没有购买改变设计，但在证书中可能有一个可能的关系到它的主要证书记录可能
让事情变得容易一点???
希望有所帮助。
感谢您的建议。在
之后添加了PrimaryCertificationID，它意识到一个人可以在历史上持有多个CCRType
。最初有另一个表（CCR）具有（CCRTypeID，PersonID）的主键。这个想法是一个人可以为每种可能的类型最多只有一个CCR。然后我们确定一个人可以认证到期，申请新的
认证，并将每个认证加入一个单独的CCR。
CCR表中有一个识别FK到CCRCertification。添加了
的PrimaryCertificationID来解决这个问题，但是现在我想要了解这是否真的描述了
现实世界的情况。
感谢您的建议，我今天要花一些时间
专注于认证之间的关系。
谢谢，
-Tom。
以防人们看到这些回复并认为问题是
解决了问题...我仍然感兴趣的是人们是否倾向于担心使用BCNF以及人们如何添加像
我在问题＃2中描述的那个。
谢谢！
-Tom。
I''m going to try to describe this situation as best as I can, but if
anything is unclear please let me know. Some of this database was
already in place before I arrived on the scene, and I don''t really
have the buyin to change it.
There is a table of certificates that a person can hold. A person may
hold certificates of various types. I need to further group a person''s
certificates together so that (for example) renewal dates can be
synchronized. There are only certain types of certificates that can be
linked together, which is defined in another set of tables. Here''s a
first-go of the model. I''ve eliminated irrelevant columns from some of
the tables.
-- This table already exists as-is
CREATE TABLE dbo.Person (
ID INT NOT NULL IDENTITY,
FirstName VARCHAR(30) NULL,
LastName VARCHAR(30) NULL)
GO
ALTER TABLE dbo.Person
ADD CONSTRAINT PK_Person PRIMARY KEY (ID)
GO
-- This table already exists as-is
CREATE TABLE dbo.Certificate (
ID INT NOT NULL IDENTITY,
PersonID INT NOT NULL,
CertificateTypeID INT NOT NULL) -- The table this FKs to is
just an ID and description. Not included here for brevity reasons
GO
ALTER TABLE dbo.Certificate
ADD CONSTRAINT PK_Certificate PRIMARY KEY (ID)
GO
ALTER TABLE dbo.Certificate
ADD CONSTRAINT FK_CertificatePerson FOREIGN KEY (PersonID) REFERENCES
dbo.Person (ID)
GO
-- This table is new and represents which types of certificates can be
linked and synchronized
CREATE TABLE dbo.CCRTypeCertificateType (
CCRTypeID INT NOT NULL, -- This FKs to a table that is
mostly just an ID and description
CertificateTypeID INT NOT NULL)
GO
ALTER TABLE dbo.CCRTypeCertificateType
ADD CONSTRAINT PK_CCRTypeCertificateType PRIMARY KEY (CCRTypeID,
CertificateTypeID)
GO
-- This table defines which actual certificates are linked together
CREATE TABLE dbo.CCRCertificate (
CCRTypeID INT NOT NULL,
PersonID INT NOT NULL,
PrimaryCertificationID INT NOT NULL, -- This is necessary
because a person can let a certification lapse and then start a new
one of the same type as the old
CertificationID INT NOT NULL )
GO
ALTER TABLE dbo.CCRCertificate
ADD CONSTRAINT PK_CCRCertificate PRIMARY KEY (CCRTypeID, PersonID,
PrimaryCertificationID, CertificationID)
GO
Other tables hang off of the CCRCertificate table. The PersonID and
CertificationID in the same table seems to violate Boyce-Codd Normal
Form, so I''ve been asked to remove the PersonID (not that BCNF was the
reason given of course, but it''s "duplicate data" since the
PrimaryCertificationID should already determine the PersonID).
What I''d like to do is:
1. Constrain the CCRCertificate table (or use additional table(s) if
necessary) so that all certifications linked as CertificationID are
from the same person.
2. Require that at most 1 certificate of each type in
CCRTypeCertificateType for a CCRType is added to CCRCertificate. In
other words, if the CCRTypeCertificateType table indicates that a
CCRType 1 is made up of CertificationType 1 and CertificationType3,
the CCRCertificate table should only include at most one certification
that has a CertificationTypeID of 1 and one that has an ID of 3 if the
CCRTypeID is 1.
I hope this isn''t too confusing. Let me know if there''s anything that
I can make clearer.
Thanks!
-Tom.
P.S. The SQL here is for illustrative purposes. I''m on a laptop that
doesn''t have SQL Server installed, so I can''t test the SQL at the
moment. Since I''m not asking for any code that might need to be tested
I hope this isn''t an issue.
What I find a bit odd is that the database has PrimaryCertificationID
and CertificationID as foreignkeys in the CCRCert table. I don''t
pretend to understand the business side here, but if a cert can be
primary to other certs, then there should be a relationship between
rows in the cert table instead, where the cert relates back to
itself. This model isn''t particularly invalid, but when you start
adding other foreign keys to that table, it''s more difficult to
maintain the integrity, as it looks like you''re experiencing.
You mentioned that you don''t have buyin to change the design, but a
possible relationship within Cert to it''s primary Cert record might
make things a bit easier???
Hope that helps.
On May 22, 4:46*pm, Eric Isaacs <eisa...@gmail.comwrote:What I find a bit odd is that the database has PrimaryCertificationID
and CertificationID as foreignkeys in the CCRCert table. *I don''t
pretend to understand the business side here, but if a cert can be
primary to other certs, then there should be a relationship between
rows in the cert table instead, where the cert relates back to
itself. *This model isn''t particularly invalid, but when you start
adding other foreign keys to that table, it''s more difficult to
maintain the integrity, as it looks like you''re experiencing.
You mentioned that you don''t have buyin to change the design, but a
possible relationship within Cert to it''s primary Cert record might
make things a bit easier???
Hope that helps.Thanks for the advice. The PrimaryCertificationID was added on after
it was realized that a Person could hold more than one CCRType
historically. Originally there was another table (CCR) which had a
primary key of (CCRTypeID, PersonID). The idea was that a person could
only have one CCR at most for each possible type. Then we determined
that a person could have a certification expire, apply for a new
certification, and have each certification joined to a separate CCR.
The CCR table had an identifying FK into CCRCertification. The
PrimaryCertificationID was added to get around that issue, but now I''m
trying to think through whether or not that is truly descriptive of
the real world situation.
Thanks to your advice, I''m going to spend some time today
concentrating on that relationship between certifications.
Thanks,
-Tom.
Just in case people see these responses and figure that the issue is a
solved matter... I''m still interested in whether or not people tend to
be concerned about using BCNF and how one might add a constraint like
the one that I described in question #2.
Thanks!
-Tom.
这篇关于建模/约束问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
