Relationship Help

L

lettyg82

I have uploaded a screen shot of my relationships window for the database I
have designed. I believe what i want is a one to many relationship. The
Parent Table is FCCaseInfo and its primary key is CaseIdNo. The child tables
should be FVVW, FVCharges, and FVDates these tables also have the CaseIdNo
(not primary, but i added this field to somehow relate all of these
together).

Any suggestions as to what my relationships should be?

Thanks!

http://cid-af7c698d9d142706.skydrive.live.com/self.aspx/.Public/Database.JPG
 
J

John W. Vinson

I have uploaded a screen shot of my relationships window for the database I
have designed. I believe what i want is a one to many relationship. The
Parent Table is FCCaseInfo and its primary key is CaseIdNo. The child tables
should be FVVW, FVCharges, and FVDates these tables also have the CaseIdNo
(not primary, but i added this field to somehow relate all of these
together).

Any suggestions as to what my relationships should be?

Thanks!

http://cid-af7c698d9d142706.skydrive.live.com/self.aspx/.Public/Database.JPG

Without more understanding of the nature of the data you're managing, I can't
comfortably say what the relationships "should" be... but what specific
problem are you having? Can you create a link from FCCaseInfo's CaseIDNo
primary key to the CaseIdNo in the FVVW child table? If not, what's causing
the problem?

Are you perhaps expecting that FVVW will automagically create a new record for
each CaseIDNo? If so, it won't: you will only want to create a record when you
have data to put into the child table. The normal way to manage this is to use
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.
 
L

lettyg82

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

John W. Vinson

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.

The foreign key fields in the three tables should be of the same datatype as
the case ID in the main table (Long Integer if the primary key is an
Autonumber). Join the tables by that field, and enforce referential integrity.
Check the "Enforce Referential Integrity" checkbox. It's not necessary to do
anything with the Cascade Update or Cascade Delete checkboxes (in fact, if
it's an autonumber the Update is irrelevant since you can't edit an autonumber
value, and Cascade Deletes is generally considered dangerous since it can lead
to ALL data for a case being deleted with one click!)

Access will automatically define the relationship as one to many, since you're
linking from a unique primary key ("one") to a non-unique foreign key
("many"). The program will automatically create indexes on the foreign key
fields, so don't do so yourself.

You're on the right track!
 
F

Fred

As I indicated in response to your other duplicate post of this quesiton

IMHO, if you put the CaseID number into those three "child tables" are are
able to put a single CaseID number into that field in each of those child
records, the you have already essentially created and told us about the
relationships, determined that they are not many-to-many, and all that
remains is to "draw the lines" between the CaseID field to the field of that
name in each of the child tables. You'll still have some secondary choices
to make (referential integrity and which, if any, unmatched records that you
so (which will presumably be those of your parent table)
 
K

KenSheridan via AccessMonster.com

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top