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.

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.

"Lee Ann" <LeeAnn@discussions.microsoft.com> wrote in message 
news:9FF441D2-159C-4F77-9948-4F6B7D292187@microsoft.com...
> 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. 

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" wrote:

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

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.



"Jeff Boyce" wrote:

> 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.
> 
> "Lee Ann" <LeeAnn@discussions.microsoft.com> wrote in message 
> news:9FF441D2-159C-4F77-9948-4F6B7D292187@microsoft.com...
> > 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. 
> 
> 
> .
> 

On Thu, 11 Feb 2010 05:19:01 -0800, Lee Ann <LeeAnn@discussions.microsoft.com>
wrote:

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

             John W. Vinson [MVP]

see comments in-line below...

"Lee Ann" <LeeAnn@discussions.microsoft.com> wrote in message 
news:D9DB23FD-A5C6-496D-80C1-3B182E8E0976@microsoft.com...
> 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.