If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Table & Relationship Advice
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. |
#2
|
|||
|
|||
Table & Relationship Advice
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" wrote in message ... 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. |
#3
|
|||
|
|||
Table & Relationship Advice
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" wrote in message ... 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. . |
#4
|
|||
|
|||
Table & Relationship Advice
see comments in-line below...
"Lee Ann" wrote in message news 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. |
#5
|
|||
|
|||
Table & Relationship Advice
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. |
#6
|
|||
|
|||
Table & Relationship Advice
On Thu, 11 Feb 2010 05:19:01 -0800, 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. 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] |
Thread Tools | |
Display Modes | |
|
|