Table & Relationship Advice

[Lee Ann]

Is it possible to post my tables and relationships for advice and to do so
privately? I posted a couple questions on this DB and based on responses,
I'm beginning to doubt my entire design. Unfortunately, this DB is of a
sensitive nature and posting the entire information to be captured on an open
forum is not an option.

Thanks in advance.

 

[Jeff Boyce]

Lee Ann

Typically, you'll find the folks here volunteer their time (the Code of
Conduct prohibits solicitation). So if you ask folks to take on extra duty,
outside the 'groups, you may want to seek out paid assistance.

Rather than take that step, could you obfuscate your data (if the data is
what's sensitive)? If it is the design itself that's proprietary, could you
use an analogy? For example, here's a simplistic design for a registration
database:

tblPerson
PersonID
FName
LName
DOB

tblClass
ClassID
ClassTitle

trelRegistration
RegistrationID
PersonID
ClassID
RegistrationDate

One record from tblPerson could have many trelRegistration records.

One record from tblClass could have many trelRegistration records.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Fred]

LeAnn

If it's a design question (as it appears to be) what you really need to give
someone is a description of the entities and process that you want to
database. SECONDARILY send your table structure.

If the description can be done generically enough to poste in this
discussion group, you might still consider that.

For fast response you'd have to send somewhere else. But if you don't mind
a slow second response feel free to send it to

North9000
at
gmail.com

 

[Lee Ann]

Fred & Jeff:

Thanks for the advice and I'll try my best to lay out what I have without
getting into too much detail. I'm not trying to solicit extra duty from
anyone, just trying to find a way to provide all the information to get
clearer answers. I have posted a couple of questions on this in recent weeks
and each answer has led to more questions to the point that I doubt my design
is correct.

I have a TblIncident:

TblIncident
IncidentID (PK)
IncidentNumber (this is actually a unique number which should never be
repeated - makes me believe it should be a PK)
IncidentDate
IncidentTime
SubjectID (FK)
LocationID (FK)
CaseworkerID (FK)
CallerName
CallerContactNumber

**Note: Each incident will include one or more subjects and one location.

TblLocation
LocationID(PK)
LocationName
LocationStreetAddress
LocationCity
LocationState
LocationContactNumber

**Note: One location will be used per incident. We have particular
locations whose information will already be loaded into this table (this will
not be information the user will be adding).

TblSubject
SubjectID (PK)
SubjectLastName
SubjectFirstName
SubjectMiddle
Additional identifying information will follow

**Note: Each subject entered will be involved in one incident at one
location. There may be more subjects involved in the same one incident.
It's conceivable that they will be involved in another incident at another
location on a different (later) date.

TblCaseWorker
CaseWorkerID (PK)
CaseWorkerLastName
CaseWorkerFirstName
etc.

**Note: One case worker will be involved in one incident at a time.

I was also contemplating another table where further information on the
subject was entered that was specific to only them. I'm not sure why I feel
I need to do this and was looking at the number of fields that would be in
the one table (23) and felt that would be to excessive.

I think where I'm running into the problems is not being really sure where
the FK fields should be in the related tables. I'm trying to think of the
"main" purpose of the DB and that is to capture these incidents. The other
information (location, subject, case worker) is related to this main
(incident) table and it makes me want to put a FK from each of these tables
into the TblIncident, but this doesn't seem to be working correctly for me.

Hope all of this makes sense and I do appreciate all the help that is given
here.

 

[John W. Vinson]

Is it possible to post my tables and relationships for advice and to do so
privately? I posted a couple questions on this DB and based on responses,
I'm beginning to doubt my entire design. Unfortunately, this DB is of a
sensitive nature and posting the entire information to be captured on an open
forum is not an option.

Thanks in advance.

I'd be willing to take a look at it if you're willing. Email jvinson <at>
wysard of info <dot> com; you could send a graphics screenshot of the
relationships window, or a stripped-down database (compacted and zipped) as
you prefer. It would help a great deal to know the real-life situation being
modeled: what kinds of entities (real-life people, things or events) it models
particularly.

 

[Jeff Boyce]

see comments in-line below...

Fred & Jeff:

Thanks for the advice and I'll try my best to lay out what I have without
getting into too much detail. I'm not trying to solicit extra duty from
anyone, just trying to find a way to provide all the information to get
clearer answers. I have posted a couple of questions on this in recent
weeks
and each answer has led to more questions to the point that I doubt my
design
is correct.

I have a TblIncident:

TblIncident
IncidentID (PK)
IncidentNumber (this is actually a unique number which should never be
repeated - makes me believe it should be a PK)

If you are assured that IncidentNumber will always be unique, it would serve
fine as a PK. Save yourself the extra field.

IncidentDate
IncidentTime
SubjectID (FK)
LocationID (FK)
CaseworkerID (FK)
CallerName
CallerContactNumber

**Note: Each incident will include one or more subjects and one location.

You'll want to re-think your table structure here. If (one) incident could
have (many) subjects, you need a table for incidents, a table for subjects,
and a table that holds valid pairs. Thus, your [SubjectID(FK)] field is not
appropriate in your tblIncident. That third table might look something
like:

trelIncidentSubject
IncidentID (or your IncidentNumber)
SubjectID (a FK from your tblSubject)

If each incident can have no more than one location, your [LocationID] field
is fine.

(same logic applies for [CaseworkerID])

(... and why use [SubjectID] but not [CallerID]? What about the notion of
having a tblPerson, then using the PersonID as needed to indicate who the
Caseworker is, the Caller is, and (in the appropriate table), the Subject?)

TblLocation
LocationID(PK)
LocationName
LocationStreetAddress
LocationCity
LocationState
LocationContactNumber

If the location is a street corner, how are you defining
[LocationContactNumber]?

**Note: One location will be used per incident. We have particular
locations whose information will already be loaded into this table (this
will
not be information the user will be adding).

TblSubject
SubjectID (PK)
SubjectLastName
SubjectFirstName
SubjectMiddle
Additional identifying information will follow

**Note: Each subject entered will be involved in one incident at one
location. There may be more subjects involved in the same one incident.
It's conceivable that they will be involved in another incident at another
location on a different (later) date.

OK, it sounds like an incident has a date. Your table has an IncidentDate
field and an IncidentTime field. Save yourself the work and let Access do
more for you by using a date/time field, and storing IncidentDateTime in a
single field (or not, as your situation requires). Just be aware that you
can use Access date/time-related functions to extract the date-only or the
time-only portion of that combined field, as needed.

TblCaseWorker
CaseWorkerID (PK)
CaseWorkerLastName
CaseWorkerFirstName
etc.

**Note: One case worker will be involved in one incident at a time.

Are you saying that a case worker can only work on one case at a time? How
will you know if a case worker is "busy" when a new case comes up?

I was also contemplating another table where further information on the
subject was entered that was specific to only them. I'm not sure why I
feel
I need to do this and was looking at the number of fields that would be in
the one table (23) and felt that would be to excessive.

Are you saying that every subject would have an entry for every one of those
23 fields? Or are you saying that each subject MAY have zero, one or ... up
to 23 attributes? If the latter, think one-to-many again and set up a table
to hold the attributes (today, numbering 23, tomorrow, maybe 100?!), and a
third table to hold the valid pairs (Subject X Attribute). If a subject X
attribute combination is NOT in that third table, the subject doesn't have
that attribute!

(just thought of this ... if a subject can be involved in more than one
incident over time, are the attributes fixed for all time, or could they be
different for subject1 X incident2 that for subject1 X incident1? If the
attributes potentially change, then you need to connect the incident X
subject X attribute -- a different 'third' table)

I think where I'm running into the problems is not being really sure where
the FK fields should be in the related tables. I'm trying to think of the
"main" purpose of the DB and that is to capture these incidents. The
other
information (location, subject, case worker) is related to this main
(incident) table and it makes me want to put a FK from each of these
tables
into the TblIncident, but this doesn't seem to be working correctly for
me.

I'll suggest that you read up on normalization and relational database
design. This may help with the notions of primary and foreign keys.

My basic approach is that anytime a record in a table "belongs" to a record
in another table, I need a "foreign key" to point back to WHICH record.
That's where the 'relationship' comes in. Think "parent" and "child" ... if
you don't include a ParentID (FK) in the child table, how do you know whose
kid it is?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.