-----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_DefendantCas es 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 ,
AppearanceTypeI D 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, AppearanceTypeI D,
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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQeLlmoechKq OuFEgEQJcQACg/UkFRDpaBaL+T/E5qc1Fr5qklBwAo IhR
EKXZntkkVxCljTB E2bEpttoP
=VhRG
-----END PGP SIGNATURE-----
fl******@bellso uth.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-JunctionCharges ToDefendantsCas e
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)
AppearanceTypeI D (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.
