Modeling/Constraint question

[Thomas R. Hummel]

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,
CertificateType ID 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_CertificateP erson 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.CCRTypeCert ificateType (
CCRTypeID INT NOT NULL, -- This FKs to a table that is
mostly just an ID and description
CertificateType ID INT NOT NULL)
GO
ALTER TABLE dbo.CCRTypeCert ificateType
ADD CONSTRAINT PK_CCRTypeCerti ficateType PRIMARY KEY (CCRTypeID,
CertificateType ID)
GO

-- This table defines which actual certificates are linked together
CREATE TABLE dbo.CCRCertific ate (
CCRTypeID INT NOT NULL,
PersonID INT NOT NULL,
PrimaryCertific ationID 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.CCRCertific ate
ADD CONSTRAINT PK_CCRCertifica te PRIMARY KEY (CCRTypeID, PersonID,
PrimaryCertific ationID, 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
PrimaryCertific ationID 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
CCRTypeCertific ateType for a CCRType is added to CCRCertificate. In
other words, if the CCRTypeCertific ateType table indicates that a
CCRType 1 is made up of CertificationTy pe 1 and CertificationTy pe3,
the CCRCertificate table should only include at most one certification
that has a CertificationTy peID 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.

[Eric Isaacs]

What I find a bit odd is that the database has PrimaryCertific ationID
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.

[Thomas R. Hummel]

On May 22, 4:46*pm, Eric Isaacs <eisa...@gmail. comwrote:

What I find a bit odd is that the database has PrimaryCertific ationID
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 PrimaryCertific ationID 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 CCRCertificatio n. The
PrimaryCertific ationID 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.

[Thomas R. Hummel]

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.

[--CELKO--]

The original design stinks on several levels
1) singular table names -- only one row?
2) IDENTITY and that silly magically universal "id"
3) too many NULL-able columns
4) data element changing names among tables
5) absurd names like "type_id" -- which is it? They are totally
different kinds of attributes
6) Vague specs like "certificat es can be linked and synchronized" ; I
will guess you mean that there are prerequisites
7) You talk expiration of certifications, but have no such data in the
schema.

CREATE TABLE Persons
(person_id INTEGER NOT NULL PRIMARY KEY, -- find a real key!!
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL);

-- a person is not an attribute of a certificate!
CREATE TABLE Certificates
(certificate_nb r INTEGER NOT NULL, -- needs actual work and probably
should be a string
certificate_typ e INTEGER NOT NULL
CHECK (certificate_ty pe IN (..)), -- use check() when list is
short and stable
prereq_certific ate_nbr INTEGER, -- null means no prerequisite
);

--this is a relationship among persons and certificates
CREATE TABLE Certifications
(certificate_nb r INTEGER NOT NULL
REFERENCES Certificates(ce rtificate_nbr)
ON UPDATE CASCADE,
person_id INTEGER NOT NULL
REFERENCES Persons (person_id)
ON UPDATE CASCADE,
PRIMARY KEY (person_id, certificate_nbr ),
start_date DATE NOT NULL,
expiration_date DATE NOT NULL,
CHECK (start_date < expiration_date ));

-- this is where I lost you, so I did this
CREATE TABLE Prerequisites
(certificate_nb r INTEGER NOT NULL
REFERENCES Certificates(ce rtificate_nbr)
ON UPDATE CASCADE,
prereq_certific ate_nbr INTEGER NOT NULL
REFERENCES Certificates(ce rtificate_nbr)
ON UPDATE CASCADE,
PRIMARY KEY (certificate_nb r, prereq_certific ate_nbr));

[Thomas R. Hummel]

On May 23, 12:54*pm, --CELKO-- <jcelko...@eart hlink.netwrote:

The original design stinks on several levels
1) singular table names -- only one row?
2) IDENTITY and that silly magically universal "id"
3) too many NULL-able columns
4) data element changing names among tables

I fully agree with you on all four of these, but as explained they
already exist in the database that I was handed. If I had the ok to
design it as I'd like or change the existing design then I certainly
would. In fact, I've spent much of this week trying to convince our
"Enterprise Architecture" team that using IDENTITY for PKs is a
mistake. I've even used many of your own arguments to do so Joe.
Unfortunately, I may lose that battle as well. The reason given (this
still makes me sick to my stomach) is, "I'm prejudiced that way" When
pressed for a reason as to *WHY*, no reason was given. But I
digress...

5) absurd names like "type_id" -- which is it? They are totally
different kinds of attributes

Again, I agree with you on this one, but it was already the
terminology being used and I don't consider this enough of an issue to
warrant the inconsistencies of doing it another way.

6) Vague specs like "certificat es can be linked and synchronized" ; I
will guess you mean that there are prerequisites

Obviously what I've written out aren't the full specs. I hoped to
provide enough information without writing a 300-page document, but I
obviously erred on the side of not providing enough information.

7) You talk expiration of certifications, but have no such data in the
schema.

This isn't a full data model, only what I thought would be relevant to
the specific questions that I had. There is a table called CCRCycle
which includes start and end dates. The DDL for that is something
along the lines of:

CREATE TABLE dbo.CCR (
CCRTypeID INT NOT NULL,
PersonID INT NOT NULL,
PrimaryCertific ationID INT NOT NULL )
GO
ALTER TABLE dbo.CCR
ADD CONSTRAINT PK_CCR PRIMARY KEY (CCRTypeID, PersonID,
PrimaryCertific ationID)
GO

CREATE TABLE dbo.CCRCycle (
CCRTypeID INT NOT NULL,
PersonID INT NOT NULL,
PrimaryCertific ationID INT NOT NULL,
CycleNumber INT NOT NULL,
EffectiveStartD ate DATETIME NOT NULL,
EffectiveEndDat e DATETIME NOT NULL )
GO
ALTER TABLE dbo.CCRCycle
ADD CONSTRAINT PK_CCRCycle PRIMARY KEY (CCRTypeID, PersonID,
PrimaryCertific ationID, CycleNumber)
GO
ALTER TABLE dbo.CCRCycle
ADD CONSTRAINT FK_CCRCycle FOREIGN KEY (CCRTypeID, PersonID,
PrimaryCertific ationID) REFERENCES CCR (CCRTypeID, PersonID,
PrimaryCertific ationID)
GO

There isn't a prerequisite between certificates, but once a person has
two certificates of particular types (defined by the
CCRTypeCertific ationType) they have to synchronize the start and end
dates of the final cycle for the two certifications. The
synchronization can go in either direction, so neither certificate
type is necessarily a primary one (PrimaryCertifi cationID was a poor
name choice).

I hope this makes things a little clearer. I'm trying to type this out
in a meeting, so I'll try to add additional pertinent information
later if I think of anything.

Thanks,
-Tom.

[Eric Isaacs]

Tom,

I'm still shooting in the dark here a little, but are all the
certificates grouped together in one group per person, or are there
multiple groups of certificates for each person? Certificates have
primary certs now, but you said that was a bad name because a single
cert is not really the "primary" and that they're just groups.

If the person has only one "group" of certificates, consider putting
the data for the "group" in the person table. If a person can have
more than one certificate "group", you'll need a table between people
and certs to logically model the "group" notion.

HTH

[Thomas R. Hummel]

On May 23, 2:58*pm, Eric Isaacs <eisa...@gmail. comwrote:

Tom,

I'm still shooting in the dark here a little, but are all the
certificates grouped together in one group per person, or are there
multiple groups of certificates for each person? *Certificates have
primary certs now, but you said that was a bad name because a single
cert is not really the "primary" and that they're just groups.

If the person has only one "group" of certificates, consider putting
the data for the "group" in the person table. *If a person can have
more than one certificate "group", you'll need a table between people
and certs to logically model the "group" notion.

HTH

They can definitely have more than one group. As an example, let's say
that there are three types of certificates, A, B, and C. A person
could get a certification of type A and B which are not tied in any
way to each other. Later, they might get a certification for type C
which is synchronized with type A. At that point they would have two
groups: (A, C) and (B). Each group would have its own set of cycles.

Thanks,
-Tom.

[--CELKO--]

> I fully agree with you on all four of these, ... If I had the ok to design it as I'd like or change the existing design then I certainly would. In fact, I've spent much of this week trying to convince our "Enterprise Architecture" team that using IDENTITY for PKs is a mistake. <<

If you want some more ammo; here is a reply to an old article on mine
that just got posted. Every year, I get a lot (at least 2 since I
started writing in the trade press and an all-time high of 15 in 2002)
of private emails that say the same thing with the painful details of
dealing with an "ID-iot" design; I cannot publish them because they
are private emails, but you might want to Google this public one.
Honest, it is real and typical, very typical:

=============
Re: Celko on SQL: Natural, Artificial and Surrogate Keys Explained

Joe hits it dead on here. I've been specialized in DB design and
programming for a dozen years now as a consultant, and every time I've
gone into a shop that uses identity columns as PKs there have been
data issues because of it.

My current client used them extensively and now they have a complex
process to merge entities because duplicates always turn up.

On top of that, they have two systems with two databases which both
include data for the same entity. They have another complex system set
up to transfer identities across the databases so that they can try to
keep them in sync. The result is a mess and there are constantly
problems with the syncing. Problems that wouldn't be there if the keys
were natural instead of derived by the system.

People complain that Joe's outlook is overly theoretical, but those
"theories" result in real-world consequences. Gravity is just a
theory, but that doesn't mean that jumping off a 10-story building is
a good idea.

http://www.informationweek.com/showA...leID=201806814

=============== =

> The reason given (this still makes me sick to my stomach) is, "I'm prejudiced that way" When pressed for a reason as to *WHY*, no reason was given. But I digress...<<

We both know that is not a reason, but an excuse :)

>but I obviously erred on the side of not providing enough information. <<

Safer in a newsgroup posting ..

>There isn't a prerequisite between certificates, but once a person has two certificates of particular types (defined by the CCRTypeCertific ationType) they have to synchronize the start and end dates of the final cycle for the two certifications. The synchronization can go in either direction, so neither certificate type is necessarily a primary one PrimaryCertific ationID was a poor name choice). <<

It sounds like a fun problem; can you catch me off line? I was out of
town for a week in the NYC area working with a small hedge fund (under
NDA just after an internal audit) and I just back for the 3-day
weekend. Junk mail, emails, junk emails, and dog poop piled up while
I was gone :)

[Thomas R. Hummel]

On May 25, 6:56*pm, --CELKO-- <jcelko...@eart hlink.netwrote:

*I fully agree with you on all four of these, ... If I had the ok to design it as I'd like or change the existing design then I certainly would. In fact, I've spent much of this week trying to convince our "Enterprise Architecture" team that using IDENTITY for PKs is a mistake. <<

If you want some more ammo; here is a reply to an old article on mine
that just got posted. *Every year, I get a lot (at least 2 since I
started writing in the trade press and an all-time high of 15 in 2002)
of private emails that say the same thing with the painful details of
dealing with an "ID-iot" design; I cannot publish them because they
are private emails, but you might want to Google this public one.
Honest, it is real and typical, very typical:

Heheh... it's funny that you should post that response to your
article. I don't seem to see the comments on the link that you
provided, but I'm familiar with it from looking for "ammo" a few days
ago. You'll notice that the name on that response is "Tom". That's not
coincidence :)

There isn't a prerequisite between certificates, but once a person has two certificates of particular types (defined by the CCRTypeCertific ationType) they have to synchronize the start and end dates of the final cycle for the two certifications. The synchronization can go in either direction, so neither certificate type is necessarily a primary one PrimaryCertific ationID was a poor *name choice). *<<

It sounds like a fun problem; can you catch me off line? *I was out of
town for a week in the NYC area working with a small hedge fund (under
NDA just after an internal audit) and I just back for the 3-day
weekend. *Junk mail, emails, junk emails, and dog poop piled up while
I was gone :)

I'll try to get an email out later today. Thanks for the help.

-Tom.