One area about which I have some reservations is that of the dates per case.
At present you have these as separate columns (fields) in a table, and as you
say that 'there will be one set of dates per case' this would mean that the
relationship would be one-to-one, i.e. the primary key of the dates table
would be the CaseIdNo, which would also be a foreign key.
A one-to-one relationship type is fine in principle; it is used to model a
situation where you have a sub-type of a type, e.g. in a staff database you
might have a table Employees and a table SalesPeople, the former including
all employees with columns for attributes which they all share, the latter
only the sales team, with columns representing attributes specific to them.
Now, you do not have a type/sub-type situation here as the dates apply to all
cases, not a subset of them, so there is no reason in fact why the various
date columns could not be included in the FVCaseInfo table. However, I'm not
convinced that would be a correct approach either. My reservations are on
two counts:
1. I would imagine there will be cases which will not have the complete set
of dates, e.g. if the case does not proceed beyond a certain stage. Correct
me if I'm wrong on this, my forensic role here has always been as an expert
witness for the prosecution rather than administrative. So there would be
Null columns for some cases. An alternative table structure, and I
personally think a more appropriate one, would be to have a table which might
be called CaseEvents or something similar, with three columns, CaseIdNo,
Event, EventDate. So you'd have one row with the case number, 'Arrest' as
the event type, and its date, another row with the same case number,
'Preliminary Hearing' as the event type, and its date, and so on. Usually a
tall skinny table is a sign of a better design than a short fat one. The
relationship from FVCaseInfo to this table would of course one one-to-many.
2. The other reservation I have is that the dates are divorced from the
other tables which reference FVCaseInfo, particularly the FVCharges table, so
if there were more than one charge per case, as a one-to-many relationship
from FVCaseInfo to FVCharges implies can be possible, there is no way of
relating any of the dates to an individual charge. Now this might not matter,
but if there are some dates which, if there were more than one charge per
case, should be associated with an individual charge then these dates would
need to be in a separate table, Charge Events say, structured in the same way,
but with a foreign key CodeID referncing the primary key of FVCharges. So
there would be two 'dates' tables, CaseEvents and Charge Events, the former
for the dates of those events which relate to the case as a whole, the other
for the dates of those events which relate to an individual charge.
When it comes to what is the primary key of these 'events' tables, whether
you need one or two such tables, this depends on whether there can be more
than one instance of the same event type for a single case or charge. If not,
then the primary key is a composite one made up of the foreign key column
(CaseIdNo or CodeID) and the event type (Arrest Date, Preliminary Hearing,
etc). If there can be more than one, e.g. if a date for a sentencing hearing
might be set, but then deferred pending the receipt of reports, and you'd
want to record both dates in the table, then the primary key would be a
composite one of the three columns, the foreign key, the event type and the
event date. An advantage of the multi-row structure which I'm suggesting
rather than multi-column structure is that you can have other columns per row
to record other information about each event, e.g. a comments column with the
reason for deferment. Beware of inappropriately recording too much as free
text 'comments' though; specific 'facts' which are values of specific
attribute type make for easy analysis of the data, whereas free text doesn’t.
Although your question is specifically about the relationship types between
FVCaseInfo and the other tables, I would suggest that you do look carefully
at the design of FVCaseInfo in the context of my comment about a fat table
being a sign in most cases of a flawed design. I can't comment in detail as
I don't sufficiently understand what attributes each column in the table
represents, but my gut instinct is that the table probably needs
'decomposing', i.e. breaking down into related tables. It does look as
though it has some column headings which represent data, i.e. attribute
values rather than attribute types, which should be stored as values in rows
in related tables rather than columns in this table, in much the same way as
I've suggested for the event dates.
Ken Sheridan
Stafford, England
FVCaseInfo = main (parent table - CaseIdNo is the primary key)
FVCharges = child table
FVVW = child table
FVDates = child table
An FVCaseInfo record will be created per defendant/docket number, each case
will have a charges record with count number (there can be more than one
count per case info record), charge code, crime name, statue, disposition per
count, sentencing per count .
This database keeps track of our Felony Domestic Violence Cases. I have four
tables that are associated with CaseIdNo. All the tables that are look up
tables are labeled LU.
Should I have the FVCaseInfo (CaseIdNo=Primary Key) table as One to many
table linked to:
FVCharges by (caseIdNo). The FVCharges Table has the counts per case,
statue,
sentencing etc.
and
FVVW table by CaseIdNo. (FVVW has the times the victim was contacted by a
victim witness specialist - this also has other stats I am keeping track of
from that specific unit). There is one FVVW Record for each case (only one
record is required per
case).
and
FVDates table I want linked to the FVCaseInfo table by CaseIdNo.
There will be one set of dates per case. (Arrest Date, Preliminary Hearing,
Arraignemnt Date, Sentencing Date, etc.)
and
I want to make sure i create the right relationship and join type. I know
my Case
Information table should be a parent table and the FVcharges table, FVVW
table and FVDates table should be children tables. I haven't joined them yet
because i want to
choose the right join type. There should not be any records created for
FVCharges, FVVW, or FVDates without first being an FVCaseInfo record created.
[quoted text clipped - 20 lines]
a Form based on the parent table, with a Subform (or subforms) based on the
child table(s), using CaseIDNo as the master/child linking field.