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]

Re: Modeling/Constraint question

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]

Re: Modeling/Constraint question

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]

Re: Modeling/Constraint question

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--]

Re: Modeling/Constraint question

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]

Re: Modeling/Constraint question

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]

Re: Modeling/Constraint question

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]

Re: Modeling/Constraint question

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--]

Re: Modeling/Constraint question

> 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/showArticle.jhtml?articleID=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]

Re: Modeling/Constraint question

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.

[Eric Isaacs]

Re: Modeling/Constraint question

On May 23, 12:03 pm, "Thomas R. Hummel" <tom_hum...@hot mail.com>
wrote:

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.
>

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 seems to me the missing entity could be the
PersonCCRTypeCe rtificationType table that would contain the FKs for
each of those tables and the start and end dates for those
certification groups, (unless those dates are calculated based on the
individual certification dates.)

[Iain Sharp]

Re: Modeling/Constraint question

On Fri, 23 May 2008 09:54:56 -0700 (PDT), --CELKO--
<jcelko212@eart hlink.netwrote:

>The original design stinks on several levels
>2) IDENTITY and that silly magically universal "id"

>
>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);

Just curious here Joe, what would be your preferred primary key for a
person?

[--CELKO--]

Re: Modeling/Constraint question

>Just curious here Joe, what would be your preferred primary key for a person? <<

What would be your preferred primary key for a thing? See how silly
that question is now? Now, if you beleive in Kabbalah mysticism then
God gave everything a number and the true name of God is a 216-digit
number. Better than a GUID, unh?

The answer is that you pick a key based on the *role* that a person
plays in your data model. If there are options, look at the degree of
trust/risk you need versus validation and verification effort/cost.

1) When I worked for the prison system, we used a "ten-card" (full set
of finger prints) to move inmates around. We needed a high degree of
certainty. Same thing for DNA in courts, hospitals, etc.

2) When I cash a check at the grocery store, my driver's license
number is good enough. They can live with a 3% bad check rate and
don't have to fingerprint me (tho Kroger's was doing that in Ohio at
one time).

3) Amazon.com, Google groups, etc. use my email address

4) I use a passport when I am a tourist overseas.

This question needs a name, since newbies ask it so often -- how about
"The magic, generic person question" , "The Kabbalah Kwestion" until
we have something better?

[Erland Sommarskog]

Re: Modeling/Constraint question

--CELKO-- (jcelko212@eart hlink.net) writes:

>>Just curious here Joe, what would be your preferred primary key for a

person? <<

1) When I worked for the prison system, we used a "ten-card" (full set
of finger prints) to move inmates around. We needed a high degree of
certainty. Same thing for DNA in courts, hospitals, etc.

Such a solution may work when the persons to put in the database are
all physical persons, and you actually can get their fingerprints. It's
not going to fly in a web shop.

2) When I cash a check at the grocery store, my driver's license
number is good enough. They can live with a 3% bad check rate and
don't have to fingerprint me (tho Kroger's was doing that in Ohio at
one time).

Yeah, but can a relational database live with 3% duplicates in its
primary key?

3) Amazon.com, Google groups, etc. use my email address

Then they are not modelling persons, they are modelling e-mail
addresses. If that is meant to be persons, they are better off using
IDENTITY, which is easier to work with than strings. You can shop as
joe.celko@hotma il.com, as joecelko@hotmai l.com, as jcelko4711@eart hlink.net,
and they get a lot of duplicates in their database. On the other
hand, if you and your wife share e-mail address, you are two persons,
and legally two customers.

4) I use a passport when I am a tourist overseas.

Not all persons have a passport. Specifically, companies never have any.

This question needs a name, since newbies ask it so often -- how about
"The magic, generic person question" , "The Kabbalah Kwestion" until
we have something better?

This is not a newbie question. Any one who has worked with data modelling
in real-life system will this to persons who only have done data
modelling in theory.

In the general case, there is no natural usable key for persons. For a
specific application there may be. But it is not always the case. Trying to
sweep it under the carpet by saying silly with things like
"The Kabbalah Kwestion" is not going to change that.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: Modeling/Constraint question

>Such a solution may work when the persons to put in the database are all physical persons, and you actually can get their fingerprints. It's not going to fly in a web shop. <<

Yes. That is why websites ask for your email address as a verifiable
identifier with easy validation. We are back to the degree of trust
in the data again, too.

>Yeah, but can a relational database live with 3% duplicates in its primary key? <<

By definition, there are no duplicates in the keys; they are in the
data. Most mailing lists I have worked with would be happy if the
error rate was <3% (six sigma and all that jazz); read the standard
contract for buying a mailing list -- the upper limit is 10% before
you can get your money back.

Every application has to set its own degree of trust/risk parameter
for their data quality. We are back to the idea of trust and risk.

>Then they are not modeling persons, they are modeling e-mail addresses. <<

No, the email is the way to get to a person (in the ISO sense of a
lawful person, human or otherwise) who plays the role of "customer"
for them. We are back to the idea of a role.

>If that is meant to be persons, they are better off using IDENTITY, which is easier to work with than strings. <<

No, it isn't. Besides the design fallacy of confusing the
representation with the fact being modeled, it is a hell of lot easier
to screw up an integer than an email address that has validation and
verification rules. How did you confirm that Cindy Lou Who was really
number 42? (Pardon the Dr. Seuss reference)

>You can shop as joe.ce...@hotma il.com, as joece...@hotmai l.com, as jcelko4...@eart hlink.net, and they get a lot of duplicates in their database.<<

Gee, they don't use one of those address scrubbing pieces of software
that would show them the duplication? This was a problem that was
solved for postal addresses decades ago; it is super easy with IP
Addresses.

But more than the obvious fix for personal email via software,
remember the idea of roles. The role of "jcelko212@eart hink.net" is
as a private citizen who buys math and CS books while the role of
"jcelko@some_co mpany.com" is as an employee who buys office supplies.
So I buy my porno at one address and never the other.n They are
different roles.

>On the other hand, if you and your wife share e-mail address, you are two persons, and legally two customers. <<

That depends on how we pay. If we use the same credit card, then are
not legally two customers; we are both liable. A company credit card
would not make ALL the stockholders separate customers. This is basic
business law!

>Not all persons have a passport. Specifically, companies never have any. <<

Nor do those lawful person travel much. Ever been on a cruise ship
with IBM sitting at your table? You seem to be missing the point
about roles..

>Any one who has worked with data modeling in real-life system will this to persons who only have done data modeling in theory. <<

Sorry, but I have a fair number of implemented data models under my
belt, AND I teach the theory. Knowing what I am doing got me the
jobs :) The last complete data model was for a company that does
software for disaster relief (very fuzzy data coming is rapidly). The
last fix was for a small company with less than 100 employees and more
than $8 billion in assets this year.

Expecting a Universal, Magical identifier for "objects",
"relationships" , "persons", etc. is very much a "newbie question" that
expects a Kabbalah number to appear for them. The "relationsh ips"
version of this is an EAV design, while exposed physical locators like
GUIDs, IDENTITY and other proprietary features are the error committed
for entities.

Sit down and re-word your posting as favoring EAV; it maps very
nicely.

>In the general case, there is no natural usable key for persons. For a specific application there may be. <<

That is what I have been saying all this time! That is why you look
for roles.

>But it is not always the case. <<

How hard did you look? Given the concept of a role played by a lawful
person, you can most often find an identifier that gives you the role;
all you care about is that role.

But it is easier to just start writing code than actually designing.
They don't have the role concept or much ability to abstract, so they
want a physical human being.

>Trying to sweep it under the carpet by saying silly with things like "The Kabbalah Kwestion" is not going to change that. <<

No, just the opposite; I am trying to expose the design fallacy of a
magical Kabbalah number for people or other entities.