Massive check-list database

[JFo]

Hi everyone

I am starting a new project for a database and am very interested to hear everyone's opinion on best design technique

Basically the database will need to store data from a number of different checklists maybe 30 or 50 relatively (in some cases almost completely) different checklists (that may have 30-50 points on each checklist). The checklists will be about different equipment and the data recorded will occasionally be similar however the similarity between all checklists is that for each piece of info recorded the user will need to be able to enter a comment and select from either yes, no or N/A.

Sometimes the data will be similar and can therefore come from a combo box or default value however overall the user will need to be able to comment over top and they will always need to be able to choose yes, no or N/A


If I create a table for each equipment type/ checklist and therefore a form or number of forms for each checklist do I have to also have 3 radio buttons for each field as well? This seems like an awful lot of data to be saved and will mean that the wheel must be re-created many times as well

A major benefit of this system I think is that it will be much easier to alter and will work great for specific validation

Your thoughts are appreciated

[rpeare]

I am not a fan of separating data that is basically all the same (a bunch of yes/no fields with comments is pretty much the same as I'm concerned) What I think I'd be inclined to do is set up one table that stored all the yes/no values regardless of what type of checklist was filled out. You can limit how many of the checkboxes show on your data entry form based on the equipment type using some simple mechanics like making use of the TAG property to make all fields with a specific TAG value visible or invisible, enabled or disabled, etc. You would just have to define which items get which type of survey and from there it's not that hard. You will always end up storing the most possible checkboxes/comments (even if they're null) but in terms of reporting/queries it is way, way, way easier to deal with one table than to create the same basic queries over and over again for different tables.

[JFo]

Ok pretty much all fields of all checklists require a yes, no or n/a and they are all different comments/ options for all equipment. Don't know tag property but was thinking having values of 1 2 or3 to for each input. If every form had generic comment boxes how would you validate/ default the inputs?

Although... Less tables and data is better I agree if functional

Thank you

[rpeare]

I don't know what your rules are or what you mean by validate/default. To me validation means making sure all the data entered is in an expected range and all the required values for a record have been entered.

Let's say you had three different checklists
all of these checklists have a certain amount of data that is identical in structure (part number, part location, etc.) now let's say you have three possible checklists:

Checklist1
Is the Part Greasy?
Is the Part Blue?
Is the Part Green?

Checklist2
Is the part Green?
Is the part Purple?
Is the part Dry?

Checklist3
Is the part Dry?
Is the part Green?
Is the part Brown?
Is the part broken?

In this example you have one checklist that has four questions, the other have three, there are overlapping questions but they are in different orders. In your table you can not make the distinction about order if someone is filling out checklist 1 for part X the questions on your form would have to be populated by a table that is storing the questions and the question order. When you make the selection you'll have to have code that fills in the questions. This is where the tag property comes in. The tag property allows you to group items on your form that you want to manipulate in any way.

The same goes for the reporting side. This is a more complex way to set it up, but as I said I prefer fewer tables and not having to rewrite queries or change data sources for forms/reports if I don't have to.

[JFo]

I think I understand what you are getting at however if I may pose an alternative example and still see if you feel the same way

Checklist1- (55 Questions)
examples
1)Support Structure
2)Test, low limit switch
3)Rail alignment
4)Wiring diagram in cabinet

Checklist2 (15 Question)
examples
1)Gate Lock
2)Floor
3)Lifting Slings
4)Certification

Checklist3 (90 Questions)
examples
1)Is oil level acceptable
2)Demarcation lines
3)Are flushings fitted
4)is pit and sump clean

As you can see the checklists are vastly different and often have nothing similar (no similar questions or even number of questions). The only thing that is the same for every checklist is the response to the question or checked piece of info

For every check point the response is
Comment and yes, no or n/a
example
Sited and conforms to regs and yes
or
Not fitted and N/A
or
Evidence of water damage and no

Furthermore yes you are correct about validation however I would like almost complete control over every possible user input in at least the following ways
- Validate to ensure a response is input (no blanks/ null values), user must enter a comment and select either yes, no or n/a
- If a number or date field is required then the validation rule enforces that only a number of date can be entered, also a mask can be used to validate if a correct email, serial number, etc is entered in comment section
- Default values are put in comment boxes for common values (i.e question 2 in checklist2 may have default value populated as sighted and user must change if not sighted) and possibly even combo boxes for some responses where a user can only pick from available options (ie checklist3 question 3 may default as not fitted in a combo box that has other options fitted above, fitted below, fitted internally, etc)
- for every response the default should be yes and the user must change the response to no or n/a as required

That is all the validation/ input masking/ default rules, etc I can think of for now but I suspect there will be more as I work my way through creating this database

Your feedback and responses are greatly appreciated

Thanks

[rpeare]

Hrm, I was under the impression that your checklists were much closer in number of responses expected based on your original post (30 - 50 questions not 15 - 90 questions) So let me ask you another question in return. Is there any expectation that you will need to report on combined data (from all different types of checklists) in the same report or will the reporting on the checklists always maintain a separation.

If you are going to, say, print a report for all the services that have been rendered on a certain vehicle that can be done with reports/subreports so I'm not so concerned about that type of reporting. If that's the extent of your reporting based on the different checklists you may, as much as I hate to say it, be better off having your checklists in different tables because of the vast difference in the pieces of data you are storing.

The only other thing, and this is extremely unconventional and I'm not sure I'd recommend it, is to have a tiered table set up

So let's say you have your base information that's the same regardless of checklist in that same table you would have the lowest possible number of checklist items (in your example 15 yes/no with comments) let's call this table tblBaseInfo. In a second table you'd have the next x questions plus the foreign key to your first table, in your example that would be a table that has the unique key of tblBaseInfo and room for an additional 40 questions/comments. When you create your data entry form you would set it up so that the subform with the addtional questions only shows up for a specific type of checklist is chosen. Let's call this table tblSecondTeir.

Finally you'd have one more table for the next highest checklist total that also had the foreign key to tblBaseInfo and room for the remaining 35 questions/comments, and again the subform for entering these answers would only appear if a specific type of survey is chosen.

The programming and setup involved in getting all of this set up is going to be complex and I don't know your skill level with vba. If it seems a bit much (because you're basically going to have to set up question text for each possible survey type as well as 'available' responses for each question) then it may be better to do separate tables.

Enclosed is an example of what I mean using forms/subforms (I typically use only unbound forms so there's are some things that will make this work more smoothly but this is just a quick example)

[JFo]

Thanks for your time rpeare, at the moment I think that the reports for each equipment checklist will actually be almost completely separate... a final report may be produced that shows different equipment inspections on same report however there is no reason I can forsee to merge the data besides data from different inspections being on the same final report one after another.

I especially appreciate your example and will try and get my head around the coding for this one. Due to the amount of questions I will probably need something similar that only opens a few questions at a time on separate pages (is this possible)? Also is it still possible to validate every input in this example as well?

However you are correct and the report will be as you say inspections on different equipment in a car and delivered as one report but not merged, so does this mean either way that I may be better off with separate tables for each checklist?

I want it to be as user friendly as possible but I also want it to be versatile and idiot-proof as well

Thanks

[rpeare]

You can perform your reporting either method you use (all checklists on one set of tables as I did in my example, or a separate table for each checklist type). If you're not fully comfortable with coding it would be easier to have each checklist on a separate table simply because there is a large difference in the number of questions per survey type.

As far as forms and the number of questions on them I would likely have a tabbed data entry form (look at tab controls), especially for the 90 item checklist. Tabbed controls let you have multiple 'pages' to do your data entry etc so that you can have all of your questions available.

Depending on if your validation rules are simple (field x must have something in it) or complex (if field x says y, then field z can only have a range of a through c) will determine how you do your validation. If it's simple and you are just going to require something to be typed in you can do that at the table level and no new record will be added unless those fields have something in them, complex validation will likely have to be done with vb and checked before a record is added to your database.

[JFo]

Thanks again... I am fairly confident in figuring out most things and with most coding I have encountered thus far. Really I am looking for the best way to design this if there is one, or whether both options are correct? Another thought about reporting/ querying from the database has also come to mind that may also effect the way I need to design my tables

I will probably want to have the ability to extract like-data from multiple tables for either scheduling or reporting, examples of this may include
- Querying which equipment for a certain inspection received a no
- Querying all equipment that was inspected on a certain date
- Querying which equipment is due for the next inspection (some check-lists may have a next inspection date field)

I will have a bit more of a play with tab controlling as well as setting up multiple smaller versions to see which method works best for now please let me know if anything else needs to be thought about before deciding on design

[JFo]

Also another thought that may change things is that every inspection there could be multiple pieces of equipment inspected sometimes with same check-list/ often with a different check-list. Additionally each piece of equipment over time could be involved in multiple inspections

[Rainlover]

rpeare

Trust you don't mind my imput. It is just that I have a different approach.

JFo

The first thing you must do is to create a properly normalised Set of Tables.

tblOne
Autonumber
Description
TypeOfInspection as a ForeginKey to TableTwo
etc

Table Two
TypeOfInspection (Autonumber) Primary Key
InspectionDescription as Foregin Key to Table Three
Yes/No

Table Three
InspectionDescription (Autonumber) Primary Key
Other

Now create a Form or Forms that list all the possible Inspections performed. This form would be Unbounded.

As the user completes the form write the Result (Using Code) to the Table.

This method allows you to have a Normalised Table Structure with the Form appearence of a denormalise Database.

Hope you understand what I am suggesting. If you need more information please ask.

[JFo]

Thanks Rainlover I appreciate your input and tbh I don't really understand what you are getting at. Through my previous posts I have fairly well revealed my entire database design however below is a list of current tables and primary/ foreign keys as per the advice given in this forum

tblClients
ClientId

tblSites
SiteID

tblClientSitesJunction
ClientID
SiteID

tblSitesContact
SitesContactID
SiteID

tblPlants
PlantID
SiteID

tblInspections
InspectionID
SiteID

tblEquipType (have not decided on proper relationship and doesn't have foreign key)
EquipTypeID

tblEquipment (have not decided on proper relationship and doesn't have foreign key)
EquipmentID

I have removed the table tblPlants however as it appeared in a number of my posts I thought I would leave it in for now. As you can see 1 client can be at many sites and 1 site can have many clients. This is solved by a junction table containing both tables primary keys as foreign keys. This part works fine for input as well however the user must manually add a specific client to a site.

now on the main form when a user selects a client and a site they then choose to add a new inspection and then add the equipment for that inspection. The relationships are then as follows

- 1 site may have many inspections however 1 inspection is only at one site
- 1 inspection may have many equipment inspected and 1 piece of equipment may be in a number of inspections over time


However this time I need a way to simply add each piece of equipment to a current inspection and really do not want the same way I added clients to a site. I want the user to add an inspection and from that form add as many pieces of equipment that they want including those inspected at a previous inspection. This data must all be handled from one form (or linked/ series)

Also there are a number pieces of different equipment as above and therefore many, many different check-lists (at the moment 25 totally different check-lists)

Let me know if this is clear or you need more info, I am eagerly awaiting your replies

[Rainlover]

tblEquipType
EquipTypeID

tblEquipment
EquipmentID
EquipTypeID

I think this is the correct Relationship.

What I tried to address was the actual inspection.
You have not listed a Table that describes the actual items inspected and the result.

Could you detail what you have in mind.

[JFo]

OK that seems right for linking equipment table to equipment type, then how can I link them to an inspection and then link them to my specific equipment tables as well as discussed above if I have a table for each check-list, for example?

tblEquipCar

and tblEquipToolBox

and tblEquipFireHydrant

I am thinking of separating all my different check-lists for ease of manipulation and validation however I still need everything attached to each other for input

The flow of command at the moment is user selects (or adds new) clients and then sites and then inspections and then adds new equipment (or adds previously inspected equipment) to inspection by selecting equipment type which opens the appropriate form for that equipment

As before a difficulty besides the vast difference in equipment inspected and check-lists used is also that multiple equipment may have multiple inspections and vice versa

I am originally trying to manage the design and get it correct for all input of data however inevitably At the end a user will want to build a report of all the information collected as per my design

This will need to include Client details, site details, inspection details, equipment details for all equipment selected and all issues ('no' selections/ non-compliances) from check-list results

Additionally I would love to be able to get other queries that can search for all equipment inspected per client, all equipment inspected on a certain date or all equipment that is non-compliant for a client, etc

As I write this I now think of having another table for Defects as well (non-compliances)

Hope this is self-explanatory enough

So I suppose another question is how to link Inspection --> Equipment --> Check-list Tables --> Non-Compliances to initial clients/sites/etc

tblInspection
tblEquipment
tblCar, tblToolBox, tblFireHydrant, etc?
tblDefects?

Is there a correct way or an ideal way of doing this?

Thanks

[JFo]

Appreciate this help