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
|
|||
|
|||
Best way to incorporate 3 job functions
I'm stumped on how to go about designing this database. It currently houses
a number of different job functions which are all tied to locations (Tbl_MAIN). One of those functions is Inspections (which are conducted by agencies) and I have this set up as an Inspection Table with a Deficiencies SubTable (and a form, sub-form for data entry) - it works great the way it is. There can be multiple Deficiencies attached to one inspection. I need to set up 2 other roles. One is Obligations/Self-Dec's and one is Internal Audits. While some of the information is the same in each (ie. a staff person assigned, status, date closed), there are also quite a few different fields required. Each function can have deficiencies assigned and some are the same for all 3 roles but some are specific to the type. For sure, each one needs to be on a seperate form but sometimes an Internal Audit will require a Self-Dec (Self-Dec's and Obligations can come from other sources too) so, I need some key information to auto-fill from the Internal Audit form to the Self-Dec form. This will be specific to the deficiency in the subtable though as the Internal Audit may turn up 5 deficiencies but only 2 require a Self-Dec. I was thinking that somehow I could achieve this by having a Self-Dec required check box beside the deficiency? The reason I want the data to auto-fill is so that the person responsible for Self-Dec's can then run a report to see what he has to do, and then complete the information for his job rather than everyone passing paper and emails back and forth as the workload is very high. I have been struggling with whether I should modify my Inspection Table and Deficiency Sub-table to include all 3 roles and have the user select either Inspection, Self-Dec, Obligation, Internal Audit from a drop-down or whether I should create a seperate table for each role. If I create a seperate table for each role, do I use the same Deficiency Sub-Table or do I also create a seperate one for each? Reporting for each role (ie. counts of how many conducted, and what deficiencies) need to be seperate although I realize this could be accomplished even in the one table. Just looking for some thoughts on which way would be the most efficient, and how I can get the Internal Audit data recorded as a Self-Dec also where required? Appreciate your help - hopefully this made sense! |
#2
|
|||
|
|||
Best way to incorporate 3 job functions
I follow some of it, but not all of it. Before worrying about forms,
you absolutely have to get the table design right. Otherwise, you'll end up with data that you cannot analyze/summarize - which is pretty worthless. The easiest way to do it (well, easy is a relative term) is to get out a pen and paper and write down a description of what's going on in a [noun] [action verb] [noun] format. For example... an Agency conducts one or more Inspections. Each Inspection may reveal one or more Deficiencies. ... so Agency---(1,M)---Inspection---(1,M)---Deficiency I'm sure that's not what you wanted to hear, but it's MUCH easier to proof a design on paper (once you've figured out how to read the diagram) than it is to do it after you have spent a ton of time designing (or mis-designing) your database. Basically, you need to identify all the nouns in the "world" you're describing, and then the verbs that connect those nouns. Just create sentences like those above. Once you have done that, you can start diagramming and adding "things" to your map with verbs to connect them. Once that's done, you can think about creating tables. But honestly, before you understand how the real world things are related, you cannot really build a database to model this situation. I would start with a barebones description. and work my way out from there. Hope this helps a little. |
#3
|
|||
|
|||
Best way to incorporate 3 job functions
What Piet Linden wrote is a pretty cool that I've not seen before to approach
the all-important step one. If I may speak directly in an effort to be helpful, your post had a lot of words but didn't have the needed ones which are definitions of your data elements. I'm assuming that your own thought process on this has this same issue/gap , and such a "cart before the horse" thinking process is hurting your progress on this. The recommended sequence would be: 1. Define the data that you are trying to database, plus your mission. Other than maybe a few references to the items of the next step (tables and relationships) leave out database terminology at this step. 2. Define a table and relationship structure that will database the above and support accomplishment of your mission. 3. Design queries, forms, reports etc. that will build on the above foundation and accomplish your mission Of course, this is just an outline. To get more help, you might want to do #1 as a post and then ask folks in the forum to recommend a table/ relationship structure. "Piet Linden" wrote: I follow some of it, but not all of it. Before worrying about forms, you absolutely have to get the table design right. Otherwise, you'll end up with data that you cannot analyze/summarize - which is pretty worthless. The easiest way to do it (well, easy is a relative term) is to get out a pen and paper and write down a description of what's going on in a [noun] [action verb] [noun] format. For example... an Agency conducts one or more Inspections. Each Inspection may reveal one or more Deficiencies. ... so Agency---(1,M)---Inspection---(1,M)---Deficiency I'm sure that's not what you wanted to hear, but it's MUCH easier to proof a design on paper (once you've figured out how to read the diagram) than it is to do it after you have spent a ton of time designing (or mis-designing) your database. Basically, you need to identify all the nouns in the "world" you're describing, and then the verbs that connect those nouns. Just create sentences like those above. Once you have done that, you can start diagramming and adding "things" to your map with verbs to connect them. Once that's done, you can think about creating tables. But honestly, before you understand how the real world things are related, you cannot really build a database to model this situation. I would start with a barebones description. and work my way out from there. Hope this helps a little. |
Thread Tools | |
Display Modes | |
|
|