Attempting to create well-designed database

I assume this is a prosecutor's office?
Some other items you may want to consider:

Codefendants
JudgeName
DefenseAttorney
TypeOfTrial(Judge or Jury)
OffenseDate
Witnesses
Address/phone info for Defendants and witnesses
SentencingGuidelinesApply
MotionForDiscoveryFiled
LabTestResultsRecieved
ReasonForContinuance
Questions:

Will you be using this to prepare court orders?
Issue subpeonas for witnesses and defendants?
Do you want to just track court dates, or use the data to manage file
content?

Comments:

You need to separate out defendants from the Case Number. The defendant
can (and will) be a defendant in other cases, so you don't want to have to
re-enter the information. Generally your table name should correspond with
your primary key - IOW, if your table is 'defendant', your pk should not be
CaseNumber, but defendant_id

tbl-Defendants
DefendatID (primary key)
FirstName
MiddleName
LastName
Suffix (Jr, Sr, III)
DOB
Race
Gender

As much as it may seem logical to use the BarNumber as the pk, I would not.
You just never know when something weird could happen - I use autonumbers
for every table, even if a pk seems to be apparent. Also, if you're in an
office with more than three prosecutors, I would consider storing last
names, first names etc rather than just ProsecutorName. With turnover and
special prosecutors, the list may grow longer than you think.

tbl-Prosecutor
ProsecuterID (Primary Key)
BarNumber
LastName
FirstName
MiddleName
Suffix

Municipal, County, State, and Federal all are types of charges - so that is
the data, not fields.

tbl-ChargeType
ChargeTypeID (auto-number assigned Primary Key)
ChargeTypeDesc (Municipal, County, State, and Federal)

Same thing.

tbl-AppearanceType (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
AppearanceTypeDesc (FirstAppearance, Arraignment, DocketSounding, PleaDay,
Trial)
Be very careful when choosing field names not to choose generic names that
will give you fits later. In tblAppearance, "Date" and "Time" are both
Access reserved words, and are bad choices for field names. Here is Tony
Toews' page on Naming Conventions.

http://www.granite.ab.ca/access/tablefieldnaming.htm

My personal preference would render the table like this (table names are
always singular, not plural):

appearance
appearance_id (auto-number assigned Primary Key)
app_court_case_id (foreign key)
app_app_type_id (foreign key)
app_prosecutor_id (foreign key)
app_date
app_time
The offer and plea information should go in the Case table, not the
appearance table, unless you feel the need to track a changing plea offer

court_case
court_case_id
cc_case_number
cc_offer
cc_plea

Where you put the disposition data will depend on your state, but it would
be best if it is kept in the same table as the individual charge, rather
than with the court case. Some states don't differentiate, but others do.
To my mind it is much easier to catch sentnecing errors if the disposition
information is kept with the cahrge, not lumped together.

I know that the court docket number is already unique, so why have a
separate autonumber id? There are several reasons, but here are a two:
- the assigneing of Docket numbers is done by the court, not your office.
Any time that the data is out of your control, I would be loathe to use that
for a pk - what if they made a mistake in the docket number, and later the
clerk wants to correct it?
- always using Long data types, with consistent names (table name + "id")
makes your life easier when writing code, recalling field names, etc
Darryl Kerkeslager
<fl******@bellsouth.net> wrote:

Work in legal office. Trying to create solid designed database
structure. This is list of tables w/fields and primary keys. Any
comments/advice greatly appreciated.
tbl-Defendants
CaseNumber (primary key)
FirstName
MiddleName
LastName
DOB
Race
Gender
tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID
tbl-Charges
ChargeID (auto-number assigned Primary Key)
Statute
Description
ChargeType
tbl-ChargeTypes (used as combo box in form)
ChargeTypeID (auto-number assigned Primary Key)
Municipal
County
State
Federal
tbl-Prosecutor (used as combo box in form)
BarNumber (Primary Key)
ProsecutorName
tbl-Appearance
AppearanceID (auto-number assigned Primary Key)
CaseNumber
AppearanceType
Date
Time
ProsecutorName
Offer
Plea
Disposition
tbl-AppearanceTypes (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
FirstAppearance
Arraignment
DocketSounding
PleaDay
Trial

Notes: Defendants may have more than one case number but case number
can only have one defendant.

Case numbers may can have more than one charge and a charge can have
many cases.

A case has a structured set of appearence types:

First Appearance
Arraignment
Docket Sounding
Plea Day
Trial

Due to various reasons, a case may have more than one of these
appearance types.

Nov 13 '05 #4

Per fl******@bellsouth.net:

comments/advice greatly appreciated.
tbl-Defendants

Lose the hyphens in object/field names.

They'll make you crazy when editing code and you try to doubleclick to select
all of the name.

--
PeteCresswell

Nov 13 '05 #5

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't use AutoNumbers as Primary Keys (PK). You can assign a Unique
Index to the AutoNumbers if you want to use them as FK linking columns
in other tables. PKs should identify the row's data in the "real
world."

Example: Will the CaseNumber identify the defendant if you went to the
courthouse and asked who was the defendant on Case # 12345? Would that
be the only defendant on that case? If there is more than one defendant
on that case then that breaks the PK. Therefore, you should have the a
Defendants table without the CaseNumber. You should have a joing table
"DefendantCases" w/ the Defendant's ID number and the CaseID. Here are
DDLs of two tables (JET SQL):

CREATE TABLE Defendants (
DefendantID COUNTER , -- the JET autonumber, a long integer
LastName VARCHAR(30) NOT NULL ,
FirstName VARCHAR(15) NOT NULL ,
MiddleName VARCHAR(15) NULL ,
DateOfBirth DATETIME NULL , -- if required change to NOT NULL
Address VARCHAR(255) NOT NULL ,
City VARCHAR(30) NOT NULL ,
State VARCHAR(2) NOT NULL ,
ZipCode VARCHAR(5) NULL , -- if required change to NOT NULL
... other personal columns ...
)

CREATE TABLE Cases (
CaseID COUNTER ,
CaseNumber VARCHAR(15) NOT NULL PRIMARY KEY ,
... other columns ...
)

CREATE TABLE DefendantCases (
DC_ID COUNTER NOT NULL , -- put a unique index on this
DefendantID INTEGER NOT NULL REFERENCES Defendants
ON DELETE CASCADE ,
CaseID INTEGER NOT NULL REFERENCES Cases
ON DELETE CASCADE ,
CONSTRAINT PK_DefendantCases PRIMARY KEY (DefendantID, CaseID)
)

In response to the poster who said table names should be in the singular
rather than the plural - use the plural 'cuz the table holds more than
one Case, Defendant, etc.; therefore, the plural is a more accurate
indication of what is in the table.

The PK for people (Defendants) is always hard, 'cuz there can be more
than one person w/ the same first, last and middle names. Even the DOB
can be the same! If you have location ID like the ZipCode you, maybe,
could use First, Last, DOB and ZipCode as a PK for Defendants. There
always is the possibility of the data-entry op misspelling one of the
names & causing a duplicate Defendant entry.

In the DefendantCases table the REFERENCES is a Foreign Key constraint
that will only permit DefendantIDs that are already in the table
Defendants and CaseIDs that are already in the table Cases.

The PK for DefendantCases is the DefendantID & the CaseID. This means
you can have more than one Defendant on the same Case, but you can't
enter more than one of the same defendant/case combinations. The ON
DELETE CASCADE clause means if the Defendant record is deleted in
Defendants it's dependent record in DefendantCases will also be deleted;
and, if the Case record is deleted in Cases it's dependent record in
DefendantCases will also be deleted.

Your table tbl_Appearances should be like this:

CREATE TABLE Appearances (
DC_ID INTEGER NOT NULL REFERENCES DefendantCases
ON DELETE CASCADE ,
AppearanceTypeID INTEGER NOT NULL REFERENCES AppearanceTypes ,
App_Date DATETIME NOT NULL , -- includes both date & time
ProsecutorID INTEGER NOT NULL REFERENCES Prosecutors ,
Offer VARCHAR(255) NULL ,
PleaID INTEGER NULL REFERENCES Pleas ,
Disposition VARCHAR(255) NULL ,
CONSTRAINT PK_Appearances PRIMARY KEY (DC_ID, AppearanceTypeID,
App_Date)
)

The DC_ID refers to the DefendantCases table, which indicates which
defendant in the case is appearing. The PK means that the defendant can
only appear at the indicated date/time for the indicated appearance
type. The defendant can appear many times on the same date for the same
or different cases.

The referenced tables AppearanceTypes, Prosecutors, and Pleas are "look
up" tables that will hold IDs, usually AutoNumbers for each
AppearanceType, Prosecutor & Plea. If the pleas can be more numerious &
variable than could be ennumerated in a list, then you may want to have
a VARCHAR data type instead of the INTEGER reference to the Pleas table.

There are probably more things here that could be addressed....

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeLlmoechKqOuFEgEQJcQACg/UkFRDpaBaL+T/E5qc1Fr5qklBwAoIhR
EKXZntkkVxCljTBE2bEpttoP
=VhRG
-----END PGP SIGNATURE-----

fl******@bellsouth.net wrote:

Work in legal office. Trying to create solid designed database
structure. This is list of tables w/fields and primary keys. Any
comments/advice greatly appreciated.
tbl-Defendants
CaseNumber (primary key)
FirstName
MiddleName
LastName
DOB
Race
Gender
tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID
tbl-Charges
ChargeID (auto-number assigned Primary Key)
Statute
Description
ChargeType
tbl-ChargeTypes (used as combo box in form)
ChargeTypeID (auto-number assigned Primary Key)
Municipal
County
State
Federal
tbl-Prosecutor (used as combo box in form)
BarNumber (Primary Key)
ProsecutorName
tbl-Appearance
AppearanceID (auto-number assigned Primary Key)
CaseNumber
AppearanceType
Date
Time
ProsecutorName
Offer
Plea
Disposition
tbl-AppearanceTypes (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
FirstAppearance
Arraignment
DocketSounding
PleaDay
Trial

Notes: Defendants may have more than one case number but case number
can only have one defendant.

Case numbers may can have more than one charge and a charge can have
many cases.

A case has a structured set of appearence types:

First Appearance
Arraignment
Docket Sounding
Plea Day
Trial

Due to various reasons, a case may have more than one of these
appearance types.

Nov 13 '05 #6

"MGFoster" <me@privacy.com> wrote:

Don't use AutoNumbers as Primary Keys (PK). You can assign a Unique
Index to the AutoNumbers if you want to use them as FK linking columns
in other tables. PKs should identify the row's data in the "real
world."

Example: Will the CaseNumber identify the defendant if you went to the
courthouse and asked who was the defendant on Case # 12345? Would that
be the only defendant on that case? If there is more than one defendant
on that case then that breaks the PK. Therefore, you should have the a
Defendants table without the CaseNumber. You should have a joing table
"DefendantCases" w/ the Defendant's ID number and the CaseID. Here are
DDLs of two tables (JET SQL):
I don't like to disagree - generally - more specifically with those who
likely have more experience than I - yet to flat-out advise to "Don't use
AutoNumbers as Primary Keys" seems to be unhelpful - as does the statement
that "PKs should identify the row's data in the "real
world." The OP was not seeking a philosophical discussion, just advice.
I'm sure that several here can attest that there are points of view on this.

And isn't this what you are advising here?
CREATE TABLE Defendants (
DefendantID COUNTER , -- the JET autonumber, a long integer -----------------------------------------------------------------------
In response to the poster who said table names should be in the singular
rather than the plural - use the plural 'cuz the table holds more than
one Case, Defendant, etc.; therefore, the plural is a more accurate
indication of what is in the table.

Again, there are two points of view on this, right? Why involve the OP in
this disagreement? Does the OP care? My advice to use the singular is
based on entity modeling - which is where the OP is right now. Here are
some other points of view:

http://discuss.fogcreek.com/joelonso...ow&ixPost=5904

http://www.hower.org/Kudzu/Articles/DBDesign/index.iwp

http://www.anticlue.net/archives/000076.htm

http://dbazine.com/gulutzan5.shtml

(etc)

Oracle seems to be consistent with preferring the plural form - but then,
this is an Access NG.

The OP was not consistent in singular vs. plural (tbl-Charges,
tbl-Prosecutor, tbl-Appearance, tbl-Defendants), so I was advising the
singular - which is not *wrong*, just not your preference. So why bring it
up in this thread?

Darryl Kerkeslager

Nov 13 '05 #7

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Row objects are supposed to model the "real-world" therefore, the PK
(which prevents duplicates by uniquely identifying the row's real-world
object) should not be an AutoNumber, which is a system-generated
identifier that has NOTHING to do w/ the real-world object. The
AutoNumber as a Unique Index is a great way to link (Foreign Key - which
requires an indexed column(s) in the parent table) to related data in
other tables, w/o including the parent's, possibly, multi-column primary
key values/columns in the related table. That's why I use them. I will
never use them as the ONLY column in a PK. My definition of the
Defendant's table did not have a PK 'cuz it would take more than one
column to uniquely identify each defendant (row). The choice of those
columns is up to the db designer 'cuz s/he has a better understanding of
the data that will be entered & the db's requirements. The AutoNumber
just "says" this is a row. The PK "says" these columns uniquely
identify this row - IOW, all the rows in the table refer to different
people.

See these sites for my views on PKs espoused by others:

http://builder.com.com/5100-6388-5034792.html
http://www.blueclaw-db.com/table_design/
http://en.wikipedia.org/wiki/Weak_entity
http://www.15seconds.com/issue/020522.htm

Singular vs. Plural - I gave an opinion & a reason 'cuz I didn't agree
w/ your statement (which you made w/o any reason). Again, our opinions.
What I think we can both agree on is consistency in naming conventions,
no matter the style. As you say there are differing opinions on naming
conventions. I just stated mine, which I consider right :-) (don't we
all?).

Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeM/2IechKqOuFEgEQK+vgCeKDY2nPmGHCu1pRQL5Eh2cAKKuHkAnR b6
3rt1EH0dCoi2AggCF40tU3V+
=45Zg
-----END PGP SIGNATURE-----
Darryl Kerkeslager wrote:

"MGFoster" <me@privacy.com> wrote:
Don't use AutoNumbers as Primary Keys (PK). You can assign a Unique
Index to the AutoNumbers if you want to use them as FK linking columns
in other tables. PKs should identify the row's data in the "real
world."

Example: Will the CaseNumber identify the defendant if you went to the
courthouse and asked who was the defendant on Case # 12345? Would that
be the only defendant on that case? If there is more than one defendant
on that case then that breaks the PK. Therefore, you should have the a
Defendants table without the CaseNumber. You should have a joing table
"DefendantCases" w/ the Defendant's ID number and the CaseID. Here are
DDLs of two tables (JET SQL):

I don't like to disagree - generally - more specifically with those who
likely have more experience than I - yet to flat-out advise to "Don't use
AutoNumbers as Primary Keys" seems to be unhelpful - as does the statement
that "PKs should identify the row's data in the "real
world." The OP was not seeking a philosophical discussion, just advice.
I'm sure that several here can attest that there are points of view on this.

And isn't this what you are advising here?

CREATE TABLE Defendants (
DefendantID COUNTER , -- the JET autonumber, a long integer

-----------------------------------------------------------------------

In response to the poster who said table names should be in the singular
rather than the plural - use the plural 'cuz the table holds more than
one Case, Defendant, etc.; therefore, the plural is a more accurate
indication of what is in the table.

Again, there are two points of view on this, right? Why involve the OP in
this disagreement? Does the OP care? My advice to use the singular is
based on entity modeling - which is where the OP is right now. Here are
some other points of view:

http://discuss.fogcreek.com/joelonso...ow&ixPost=5904

http://www.hower.org/Kudzu/Articles/DBDesign/index.iwp

http://www.anticlue.net/archives/000076.htm

http://dbazine.com/gulutzan5.shtml

(etc)

Oracle seems to be consistent with preferring the plural form - but then,
this is an Access NG.

The OP was not consistent in singular vs. plural (tbl-Charges,
tbl-Prosecutor, tbl-Appearance, tbl-Defendants), so I was advising the
singular - which is not *wrong*, just not your preference. So why bring it
up in this thread?

Darryl Kerkeslager

Nov 13 '05 #8

Lea

Sorry it has taken so long to respond to all. Seems like I'm always
putting out fires at work so I've been extremely busy. I appreciate all
the input and this morning I will sit down and analyze all this input
to get this project a little further along (as soon as I finish the
latest (yesterday's newest) Visio project.

I'll post back here once I get more advanced into the database details.

Once again, thanks for all the advice. It's great to have this user's
group and it's support.
MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Row objects are supposed to model the "real-world" therefore, the PK
(which prevents duplicates by uniquely identifying the row's real-world object) should not be an AutoNumber, which is a system-generated
identifier that has NOTHING to do w/ the real-world object. The
AutoNumber as a Unique Index is a great way to link (Foreign Key - which requires an indexed column(s) in the parent table) to related data in
other tables, w/o including the parent's, possibly, multi-column primary key values/columns in the related table. That's why I use them. I will never use them as the ONLY column in a PK. My definition of the
Defendant's table did not have a PK 'cuz it would take more than one
column to uniquely identify each defendant (row). The choice of those columns is up to the db designer 'cuz s/he has a better understanding of the data that will be entered & the db's requirements. The AutoNumber just "says" this is a row. The PK "says" these columns uniquely
identify this row - IOW, all the rows in the table refer to different
people.

See these sites for my views on PKs espoused by others:

http://builder.com.com/5100-6388-5034792.html
http://www.blueclaw-db.com/table_design/
http://en.wikipedia.org/wiki/Weak_entity
http://www.15seconds.com/issue/020522.htm

Singular vs. Plural - I gave an opinion & a reason 'cuz I didn't agree w/ your statement (which you made w/o any reason). Again, our opinions. What I think we can both agree on is consistency in naming conventions, no matter the style. As you say there are differing opinions on naming conventions. I just stated mine, which I consider right :-) (don't we all?).

Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeM/2IechKqOuFEgEQK+vgCeKDY2nPmGHCu1pRQL5Eh2cAKKuHkAnR b6
3rt1EH0dCoi2AggCF40tU3V+
=45Zg
-----END PGP SIGNATURE-----
Darryl Kerkeslager wrote:
"MGFoster" <me@privacy.com> wrote:
Don't use AutoNumbers as Primary Keys (PK). You can assign a UniqueIndex to the AutoNumbers if you want to use them as FK linking columnsin other tables. PKs should identify the row's data in the "real
world."

Example: Will the CaseNumber identify the defendant if you went to thecourthouse and asked who was the defendant on Case # 12345? Would thatbe the only defendant on that case? If there is more than one defendanton that case then that breaks the PK. Therefore, you should have the aDefendants table without the CaseNumber. You should have a joing table"DefendantCases" w/ the Defendant's ID number and the CaseID. Here areDDLs of two tables (JET SQL):

I don't like to disagree - generally - more specifically with those who likely have more experience than I - yet to flat-out advise to "Don't use AutoNumbers as Primary Keys" seems to be unhelpful - as does the statement that "PKs should identify the row's data in the "real
world." The OP was not seeking a philosophical discussion, just advice. I'm sure that several here can attest that there are points of view on this.
And isn't this what you are advising here?

CREATE TABLE Defendants (
DefendantID COUNTER , -- the JET autonumber, a long integer

-----------------------------------------------------------------------
In response to the poster who said table names should be in the singularrather than the plural - use the plural 'cuz the table holds more thanone Case, Defendant, etc.; therefore, the plural is a more accurate
indication of what is in the table.

Again, there are two points of view on this, right? Why involve the OP in this disagreement? Does the OP care? My advice to use the singular is based on entity modeling - which is where the OP is right now. Here are some other points of view:

http://discuss.fogcreek.com/joelonso...ow&ixPost=5904
http://www.hower.org/Kudzu/Articles/DBDesign/index.iwp

http://www.anticlue.net/archives/000076.htm

http://dbazine.com/gulutzan5.shtml

(etc)

Oracle seems to be consistent with preferring the plural form - but then, this is an Access NG.

The OP was not consistent in singular vs. plural (tbl-Charges,
tbl-Prosecutor, tbl-Appearance, tbl-Defendants), so I was advising the singular - which is not *wrong*, just not your preference. So why bring it up in this thread?

Darryl Kerkeslager

Nov 13 '05 #9

"MGFoster" <me@privacy.com> wrote in message
news:xd*****************@newsread3.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Row objects are supposed to model the "real-world" therefore, the PK
(which prevents duplicates by uniquely identifying the row's real-world
object) should not be an AutoNumber, which is a system-generated
identifier that has NOTHING to do w/ the real-world object.

Sure. So what would you use as a primary key for defendents?

Mike

Nov 13 '05 #10

personally I'd have a People table.

and a PersonID in the Defendents table referencing it. And anywhere else.

People have roles, one minute he's a prosecuting officer, next minute a
defendent, next minute a witness.

You might think unlikely, but it does happen, especially the
defendent/witness thing, and you really really would want to know if it was
the same person wouldn't you?

Mike

<fl******@bellsouth.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Work in legal office. Trying to create solid designed database
structure. This is list of tables w/fields and primary keys. Any
comments/advice greatly appreciated.
tbl-Defendants
CaseNumber (primary key)
FirstName
MiddleName
LastName
DOB
Race
Gender
tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID
tbl-Charges
ChargeID (auto-number assigned Primary Key)
Statute
Description
ChargeType
tbl-ChargeTypes (used as combo box in form)
ChargeTypeID (auto-number assigned Primary Key)
Municipal
County
State
Federal
tbl-Prosecutor (used as combo box in form)
BarNumber (Primary Key)
ProsecutorName
tbl-Appearance
AppearanceID (auto-number assigned Primary Key)
CaseNumber
AppearanceType
Date
Time
ProsecutorName
Offer
Plea
Disposition
tbl-AppearanceTypes (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
FirstAppearance
Arraignment
DocketSounding
PleaDay
Trial

Notes: Defendants may have more than one case number but case number
can only have one defendant.

Case numbers may can have more than one charge and a charge can have
many cases.

A case has a structured set of appearence types:

First Appearance
Arraignment
Docket Sounding
Plea Day
Trial

Due to various reasons, a case may have more than one of these
appearance types.

Nov 13 '05 #11

MGFoster

Mike MacSween wrote:

personally I'd have a People table.

and a PersonID in the Defendents table referencing it. And anywhere else.

People have roles, one minute he's a prosecuting officer, next minute a
defendent, next minute a witness.

You might think unlikely, but it does happen, especially the
defendent/witness thing, and you really really would want to know if it was
the same person wouldn't you?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have identified a common problem in db design - people. What is the
unique identifier(s) for a person?

Name - there are other people w/ the same name(s).
Address - there could be people at the same address w/ the same name.
Phone, PostalCode - same as Address
ID number provided by gov't - Not all gov'ts give ID numbers. Some
recycle the numbers (USA: SSN is recycled).
DNA - yeah, right! :-)

This is why I left a PK off the Defendants table. I believe the person
who is most familiar w/ the person data can come up w/ the column(s)
that uniquely identifies the person - like requiring a gov't ID number
(driver's license, state/province ID number), but the db designer has to
know that the people in the db will have that ID.

Example of BAD PK in a person table

CREATE TABLE persons (
ID COUNTER primary key,
fname varchar(15),
lname varchar (30),
mi char(1)
)

Data:

ID (PK) fname lname mi
1 Joe Blow L
2 Joe Blow L
3 Joe Blow L

See, the PK is unique (ID: 1, 2, 3), but the person has the same name.
Same person or 3 different persons w/ the same name? What to do, what
to do?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeRS3oechKqOuFEgEQLU/QCgmdcySEGiTCxWbMOQ43lqwqfYZs4AoJhC
aWg30vI1E2ZlyfucZMj8uceU
=zg5/
-----END PGP SIGNATURE-----

Nov 13 '05 #12

John Baker

MGFoster wrote:

Row objects are supposed to model the "real-world" therefore, the PK
(which prevents duplicates by uniquely identifying the row's real-world
object) should not be an AutoNumber, which is a system-generated
identifier that has NOTHING to do w/ the real-world object. The
AutoNumber as a Unique Index is a great way to link

I thought I was going to be disagreeing with you, but it turns out we're on the same page.

Autonumbers make great PHYSICAL keys. As a LOGICAL key, using them may well get you into trouble.
--
To Email Me, ROT13 My Shown Email Address

Nov 13 '05 #13

"MGFoster" <me@privacy.com> wrote

You have identified a common problem in db design - people. What is the
unique identifier(s) for a person? [snip]
This is why I left a PK off the Defendants table. I believe the person
who is most familiar w/ the person data can come up w/ the column(s)
that uniquely identifies the person - like requiring a gov't ID number
(driver's license, state/province ID number), but the db designer has to
know that the people in the db will have that ID.

Example of BAD PK in a person table [snip] ID (PK) fname lname mi
1 Joe Blow L
2 Joe Blow L
3 Joe Blow L

See, the PK is unique (ID: 1, 2, 3), but the person has the same name.
Same person or 3 different persons w/ the same name? What to do, what
to do?

Having dealt with that situation for years, I can tell you that the only
thing we have that is close to a unique id is a fingerprint-indexed id
number. However, this number is not instantly available (could take weeks),
and is sometimes never available, as for fugitives and juveniles in some
states. So you cannot rely on the only reliable identifier, as it may not
even be available.

To hunt for a unique ID is a wild goose chase, especially when you are often
forced to deal with the person by the name that they were indicted under -
despite the fact that it is a known alias, with a fake Social Security
Number, date of birth, etc. Further complicating that is that the most
frequent targets of identity theft are close assocaites of criminals -
brothers, sisters, and other local criminals. No matter how large a set of
identifiers/fields you include to compose a pk, they are meaningless against
identity theft.

The DBA must have complete control over the pk, to create and destroy them
as fitting when aliases are discovered, and true identituies become known.
While autonumber fields are slightly fallible, the only logical choice for a
pk in this situation is some form of sequential field.
Darryl Kerkeslager

Nov 13 '05 #14

Per MGFoster:

This is why I left a PK off the Defendants table. I believe the person
who is most familiar w/ the person data can come up w/ the column(s)
that uniquely identifies the person - like requiring a gov't ID number
(driver's license, state/province ID number), but the db designer has to
know that the people in the db will have that ID.

Based on experience, I wouldn't even consider trying to create an intelligent PK
for people. AutoNumber, GUID, or some other blind/dumb number is the only
reliable way to go.

In fact, my overwhelming predisposition is to have meaningless unique numbers as
keys on virtually all my tables (exceptions are things like State lookup tables
where the data is pretty much static and limited.
--
PeteCresswell

Nov 13 '05 #15

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Without getting into identity theft situations....

I believe you're just emphasizing my point: It's hard to uniquely
identify a person. Therefore, we, db designers, have to be willing to
assign the best PK we can on something as amorphous as a person's
identity. I do not believe that that PK is, logically, a sequential
number. It has to be pieces of data that at best come CLOSE to uniquely
identifying the person/cow/car/spatula etc. Close being the acceptable
variance/error rate in the db. IOW, the granularity of the uniqueness
identifiers depends on the db's/client's requirements and data
availability.

Re: waiting for finger-print index number:

There was a db programmer, I can't remember his name, who wrote that if
there is a data row that can't be saved to the table 'cuz it violates
key requirements, then it should be saved to a temp. table until the
required keys can be filled (within days) - then saved to the production
table.

I had a situation like this - data download that was missing key values
(SSN). I just created identifiable pseudo-numbers in place of the key
values so the data could be imported. I also created some reports that
showed the rows that had pseudo numbers and needed attention. The
client accepted this work-around 'cuz their requirements allowed it; and
I'd created import routines that replaced the pseudo number when future
downloads produced the valid key values.

Now you're going to say I created a PK on unassociated numbers (system
generated, like AutoNumbers). Well, I did, but only because I knew that
in the future the associated number (SSN - a number assigned to unique
individuals) would be available. I understood the requirements of the
db & could design a method that would, eventually, get the associated
value as the unique identifier for the person. The client understood
and accepted that there might be duplicates until the defining values
were downloaded.

G'Night
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeSMAoechKqOuFEgEQJiPQCg9gO96jG1ZOIt0Newv+mpDE 8Qe7cAn2BU
HN+poKWU2Hm0pSN1Ur0T9GTV
=LC32
-----END PGP SIGNATURE-----
Darryl Kerkeslager wrote:

Having dealt with that situation for years, I can tell you that the only
thing we have that is close to a unique id is a fingerprint-indexed id
number. However, this number is not instantly available (could take weeks),
and is sometimes never available, as for fugitives and juveniles in some
states. So you cannot rely on the only reliable identifier, as it may not
even be available.

To hunt for a unique ID is a wild goose chase, especially when you are often
forced to deal with the person by the name that they were indicted under -
despite the fact that it is a known alias, with a fake Social Security
Number, date of birth, etc. Further complicating that is that the most
frequent targets of identity theft are close assocaites of criminals -
brothers, sisters, and other local criminals. No matter how large a set of
identifiers/fields you include to compose a pk, they are meaningless against
identity theft.

The DBA must have complete control over the pk, to create and destroy them
as fitting when aliases are discovered, and true identituies become known.
While autonumber fields are slightly fallible, the only logical choice for a
pk in this situation is some form of sequential field.

Nov 13 '05 #16

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:Hc********************@comcast.com...

Having dealt with that situation for years, I can tell you that the only
thing we have that is close to a unique id is a fingerprint-indexed id
number.

Do you mean having dealt with modelling people or the criminal justice
system?

I think people (and their frequent accompanying entity, addresses) actually
make awful 'examples' for logical modelling discussions. I remember reading
a book by a well known and respected author on database design with MS
technologies in which the author emphasises the importance of 'natural'
keys, but later says words to the effect of 'slap an autonumber on it'.

I'm a great believer in the correctness of the relational model, and the
more I read, especially things by the 'evangelists' like C J Date and Fabian
Pascal, the more right I think they are right. BUT I really cannot find a
'natural' key, for people. OK, it depends upon the domain of discourse, so
if you can say that you will only deal with people of a certain nationality,
say UK, AND you can guarantee that you will always be able to find out,
accurately, their National Insurance number (our equivalent of SSN) then
fine, go ahead. My experience is that the number of times when you have a SO
CALLED natural identifier which is reliable, minimal, stable etc. is none.
And what makes it 'natural'. Every one of the things that is usually
suggested is a number/label which was invented precisely for the purpose of
uniquely identifying the person. If relational theory had existing when the
US SSN system was defined then wouldn't people have said 'that's not a
natural identifier'?

The distinction between natural and artificial identifiers is not very
great. Non-existant usually. In a marks database I did for a HE
establishment we had one, for the students, their student number. In what
way was that 'natural'? It was assigned, by another computer system, when
the students enrolled, and I just 'borrowed' it for the separate marking
system (in fact, I've got a feeling I had an autonumber as a surrogate). But
if that number hadn't existed I would have invented my own, autonumber of
otherwise.

Of course it's important to check for duplicates, FirstName, LastName, DOB
makes a very good one. But even then it'll happen, but at least you can ask
the user.

My 2p worth, Mike

Nov 13 '05 #17

"Mike MacSween" <mi************************@btinternet.com> wrote

Do you mean having dealt with modelling people or the criminal justice
system?
Criminal Justice for many years, database modeling of offenders for only a
few.
And what makes it 'natural'. Every one of the things that is usually
suggested is a number/label which was invented precisely for the purpose of uniquely identifying the person. If relational theory had existing when the US SSN system was defined then wouldn't people have said 'that's not a
natural identifier'?

That's an extremely good point. The Social Security Number is nothing more
than a sequential (meaningless) id number assigned by the Social Security
Administration, which is no more nor less reliable than any other
number-assigning agency. The same goes for the Selective Service Number,
Driver's License Number, Immigration and Naturalization Service (INS)
Number, or Military ID number. The FBI also uses a "FBI Number."

The reasons that I see that you *must* use a meaningless
(sequential/autonumber) pk for people are:

1. The only true 'natural' identifiers - fingerprints, retina scans, DNA
code - are unavailable for most people. I'm sure we'll all get there at
some point, but for now, you need to enter the person in the database,
whether you have their DNA or not.

2. The usual identifiers - name, dob, race, sex, height, weight, tatoos,
etc, are all changeable (dob excepted), and so common that you would need to
use several fields even to distinguish people in a medium-sized city. One
set of identical twins would even blow that out of the water.

3. Identity fraud is not some sort of far-fetched scheme invented in the
last ten years. Every time Bob Doe is stopped for Driving with No License
and Speeding, and says that he is in fact his brother John, that's identity
fraud, and it's been happening for decades.

4. People come into the database from other countries, where names, dob, and
race are oftentimes fuzzy, if even recorded at all. Even if recorded, we
often have to artificially force names/ethnicity into our system when their
native names/classification does not fit our own scheme.
Darryl Kerkeslager

Nov 13 '05 #18

rkc

Darryl Kerkeslager wrote:

1. The only true 'natural' identifiers - fingerprints, retina scans, DNA
code - are unavailable for most people. I'm sure we'll all get there at
some point, but for now, you need to enter the person in the database,
whether you have their DNA or not.

So what do you do to insure the combination of data in a record is
unique? Do you even try in the model or do you rely on application
level checks?

Nov 13 '05 #19

"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote

Darryl Kerkeslager wrote:
1. The only true 'natural' identifiers - fingerprints, retina scans, DNA
code - are unavailable for most people. I'm sure we'll all get there at
some point, but for now, you need to enter the person in the database,
whether you have their DNA or not.

So what do you do to insure the combination of data in a record is
unique? Do you even try in the model or do you rely on application
level checks?

Hah! Good question. When I said they were unavailable for most people, I
meant developers - fortunately, the fingerprint data is available to my db
indirectly for most offenders. I rely indirectly on their state police/FBI
number, which is indexed to fingerprint data. This provides a uniqueness
constraint on above 96% of offenders. I guess this would technically be a
foreign key, but to a table under somebody else's control, in somebody
else's db. Similarly, I do a uniqueness constraint on Social Security
Number - again, a foreign key referencing somebody else's pk - which I just
have to hope is reliable. Nearly 100% provide a SSN. However, since they
are providing it, it is more prone to error, and realistically, the
possibility of a collision with another SSN in this db is incredibly small
with a 9-digit SSN.

Using a common sense "look" by the person entering the new offender -
similar names, dob, address history, race, sex, etc could eliminate 99% of
the duplicates in the remaining 1% - but only if the person entering the new
data exercises due diligence. Realistically, our choices are to accept the
possibility of error and attempt to minimize it with good procedures and
diligence, or set up some sort of mirror database of "pending verification"
people while we wait for fingerprints to be compared and indexed - and throw
out all the old historical data where no fingerprint was done for whatever
reason, and never will be done. The latter choice will not happen.

So I have a person entered twice, or more, under three, four, or more
aliases. If or when it's discoverd, the records are merged under the true
name. I don't foresee a perfect answer just within the db, nor even in
application code.
Darryl Kerkeslager

Nov 13 '05 #20

Per Darryl Kerkeslager:

So I have a person entered twice, or more, under three, four, or more
aliases. If or when it's discoverd, the records are merged under the true
name. I don't foresee a perfect answer just within the db, nor even in
application code.

How about a "People" table and a "PersonRole" table? Last nite I was thinking
that just bc of the prosecutors and defendents, but dropped it because it
sounded too much like a techweenieish solution that added too much complexity.

But now that you've brought up aliases....tblPersonName....where 99% of the
people have only one tblPersonName entry with tblPersonName.PrimaryName=True.

This would add significantly to the coding effort but, especially if one assumes
more requirements vis-a-vis people's names/roles may emerge, might make for a
more robust structure.

I've done something like this on a couple of projects only with post office
addresses. It was definately a hassle to implement...but in both cases later
events made me glad I did it.
--
PeteCresswell

Nov 13 '05 #21

"(Pete Cresswell)" <x@y.z> wrote in message
news:c5********************************@4ax.com...

Per Darryl Kerkeslager:
So I have a person entered twice, or more, under three, four, or more
aliases. If or when it's discoverd, the records are merged under the true
name. I don't foresee a perfect answer just within the db, nor even in
application code.

tblPerson:
PersonID (PK)
FirstName
LastName

tblAlias:
AliasID (PK)
PersonID (FK)
AliasFirstName
AliasLastName

?

Sure, if you discover that there are 2 records in tblPerson that are
actually the same person then you have to move the 'incorrect' one to
tblAlias.

Difficult problem domain you got there. Where people _deliberately_ lie
about their name!!
How about a "People" table and a "PersonRole" table? Last nite I was
thinking
that just bc of the prosecutors and defendents, but dropped it because it
sounded too much like a techweenieish solution that added too much
complexity.

I think every should have a People and a PeopleRole structure. There are
far too many instances where the same person can have more than one reason
to be in the database. The duplication is unacceptable.

Suppliers are often customers, of the same company.
Students leave and become teachers, at the same place.
Law enforcement officers sometimes commit crimes.

Mike

Nov 13 '05 #22

"Mike MacSween" <mi************************@btinternet.com> wrote

"(Pete Cresswell)" <x@y.z> wrote
Per Darryl Kerkeslager:
So I have a person entered twice, or more, under three, four, or more
aliases. If or when it's discoverd, the records are merged under the truename. I don't foresee a perfect answer just within the db, nor even in
application code.
tblPerson:
PersonID (PK)
FirstName
LastName

tblAlias:
AliasID (PK)
PersonID (FK)
AliasFirstName
AliasLastName

Sure, if you discover that there are 2 records in tblPerson that are
actually the same person then you have to move the 'incorrect' one to
tblAlias.

Difficult problem domain you got there. Where people _deliberately_ lie
about their name!!
How about a "People" table and a "PersonRole" table? Last nite I was
thinking
that just bc of the prosecutors and defendents, but dropped it because

it sounded too much like a techweenieish solution that added too much
complexity.

I think every should have a People and a PeopleRole structure. There are
far too many instances where the same person can have more than one reason
to be in the database. The duplication is unacceptable.

Suppliers are often customers, of the same company.
Students leave and become teachers, at the same place.
Law enforcement officers sometimes commit crimes.

Pete and Mike,

I had not considered the personRole table in relation to this problem (I
tried it earlier in a project and abandoned it for the same reason, Pete -
overly complicating things). Although it may be of use in some
ircumstances, understand that our main focus if offenders - so if a poice
officer then became an offender, I don't know as we would really need to
worry about that. The data we would keep on a police officer would be
normal contact information - name, phone, agency, notes. I would probably
just mark the contact as inactive with a note, and just leave the two as
wholly separate.

While police officers that pass the bar and become attorneys might end up
with two entries, I would have to consider that a minor annoyance, not a
critical flaw. And, I would rather have tow entries for a female attorney
who married, and risk duplication, than not have the name in the db that we
all know her by. For offenders, the uniqueness is critical; its not so much
for other persons.
Darryl Kerkeslager

Nov 13 '05 #23