Exploring possibilities

[TraumaDoc]

I'm developing a database that involves scoring a facility on a list of about 90 different criteria. I have a table that collects a score for each criteria, for each facility, for each date of assessment, and in general my tables and relationships are working well. Now I'm to the point of making data entry easy and what I'd like to create is a form that has a single place to enter "date of assessment", then lists each of the 90 criteria with a place to record the grade. And all of this is aggregated in the table so that queries and reports can be developed.

In my current table arrangement I have it set to record date of assessment and grade separately for each criteria which means inputting the same date 90 times. Is there a better way to organize data entry in a form? Can I apply one date to multiple criteria somehow?

This is not a regularly scheduled assessment and the intervals are not the same for all facilities.

I look forward to your thoughts.

Stephen

 

[Galaxiom]

You would do it with a form for the date and FacilityID plus any other common factors relating to the assessment. Then a subform for the criteria.

BTW Does a facility always have all of the ninety criteria?

Will there potentially be more criteria added in the future?

Are the scores for the criteria summed or averaged for a total score.

 

[561414]

It sounds like that date should belong to your Facilities assesments table, but it all depends on your design. Is it always going to be the same date for all criteria or is it going to change?

 

[TraumaDoc]

Thanks !

Each facility assessment does always include all 90 criteria.

Yes, potentially new criteria could be added. But not likely big numbers of additions.

The scoring is qualitative like stoplight scoring ie red, yellow or green. No need to sum or average, but a need to total all reds, all yellow etc

My current setup is:
Facilities table: contains name of facility, point of contact, date of last assessment and some other demographic info
Grade table: only a list of red, yellow, green to be used as a lookup table
Assessment table: Facility (lookup from Facility table), date of assessment, criteria ID (lookup from a criteria table) and Grade (lookup from the grade table). Facility, Date of assessment and criteria ID are all identified as primary keys for this table since the 3 together should never be replicated.

 

[561414]

This design could potentially be better:

facilities
  facilityId (primary key)
  name
  pointOfContact
  lastAssessmentDate
  demographic

grades
  gradeId (primary key)
  color

assessments
  assessmentId (primary key)
  facilityId (foreign key)
  assessmentDate

assessment_details
  assessmentDetailId (primary key)
  assessmentId (foreign key)
  criteriaId (foreign key)
  gradeId

criteria
  criteriaId (primary key)
  criteria

With that design, you only specify the date of the assessment in the parent table and then you add all 90 criteria as children, related to the assessment, where it's always gonna be the same date.

 

[561414]

Additionally, you could also remove criteria tables and, instead of an assessment_details table, store the 90 fields in your assessments table like this: criteria1_grade, criteria2_grade, ..., criteriaN_grade. No normalization issues there, each assessment is unique and the 90 criteria are essential parts of the assessment, so it's just a table with many fields.

 

[TraumaDoc]

Additionally, you could also remove criteria tables and, instead of an assessment_details table, store the 90 fields in your assessments table like this: criteria1_grade, criteria2_grade, ..., criteriaN_grade. No normalization issues there, each assessment is unique and the 90 criteria are essential parts of the assessment, so it's just a table with many fields.

Appreciate that idea. I was thinking that since I might need to edit criteria or add criteria in the future it would be best to have them in a separate table.

 

[TraumaDoc]

This design could potentially be better:

facilities
  facilityId (primary key)
  name
  pointOfContact
  lastAssessmentDate
  demographic

grades
  gradeId (primary key)
  color

assessments
  assessmentId (primary key)
  facilityId (foreign key)
  assessmentDate

assessment_details
  assessmentDetailId (primary key)
  assessmentId (foreign key)
  criteriaId (foreign key)
  gradeId

criteria
  criteriaId (primary key)
  criteria

With that design, you only specify the date of the assessment in the parent table and then you add all 90 criteria as children, related to the assessment, where it's always gonna be the same date.

I like this idea and see how it fits together. My only problem is that my "TBL assessment details" does a lookup from the Tbl_ surveys and it pulls the assessment ID as number, when the user really needs to select a facility/date of assessment combo. The lookup from assessment ID to the survey table works perfectly I just cant get it to pull facility name. It does pull the date associated with the survey. This is despite selecting facility name from available fields and putting in selected fields. What am I missing?

 

[561414]

I think you are only missing some UI design principles.

This database schema is typically solved with a form/subform design. Your form should be bound to surveys and your subform should be bound to assessment_details. Do you know how to do that?

Another approach (recommended by me, and it's an approach you probably know better) is to have form with a list of surveys from which you can add, modify or delete surveys. Then when you select one survey, it takes you to another form where the only record you can see is the selected survey, this form will have a subform with the assessment details of the selected survey. It could have a button that adds all of your criteria with a default value using VBA.

You can post a sample of your progress so I can see how you are handling it.

 

[Galaxiom]

Additionally, you could also remove criteria tables and, instead of an assessment_details table, store the 90 fields in your assessments table like this: criteria1_grade, criteria2_grade, ..., criteriaN_grade. No normalization issues there, each assessment is unique and the 90 criteria are essential parts of the assessment, so it's just a table with many fields.

Absolutely do not do this. A design like that is not extensible without redesigning the tables and forms. Bad bad bad.

 

[Galaxiom]

Have a look at this sample database. It has a novel form structure that is remarkably close to what you are trying to build.

It shows how to prepopulate virtual records on a continuous subform with the records only becoming real when a value is entered. It is remarkably simple with very little VBA required.

 

[561414]

A design like that is not extensible without redesigning the tables and forms. Bad bad bad.

If you want to add a criteria: add a field to the table, then add a control to the form. It is very simple. You can not add fields without adding fields and you can not add controls without adding controls.

 

[KitaYama]

then add a control to the form

I think the whole purpose of a good schema is the flexibility of the database against future changes, without the necessity in changing the design of the objects.

 

[ebs17]

If you make a form entry and need a separate date for each criterion, which is usually the same, you can reset the default value for the control element in the form:

Private Sub DateControl_AfterUpdate()
   Me.DateControl.DefaultValue = Str(CDbl(Me.DateControl))
End Sub

Depending on your regional settings, the right side may look even simpler.

 

[MajP]

If you want to add a criteria: add a field to the table, then add a control to the form. It is very simple. You can not add fields without adding fields and you can not add controls without adding controls.

That is pretty much the definition of non-extensible. No serious survey especially over 90 questions would ever choose such a design. Does not matter if it is normal or not. If the database is not scalable, maintainable, and labor intensive it does not really matter.
Here is a good design

At Your Survey

At Your Survey (AYS) is a full featured application that allows users to create their own surveys...

www.rogersaccesslibrary.com

 

[561414]

@Galaxiom
The schema you have provided shows a composite primary key, which does align to OP's initial schema, but contains no relationships. Therefore, no referential integrity is applied, data is on the fly and actions do not cascade. It can be done, but it is not advisable, as the user will have to take care of cascading changes, instead of the database engine providing that very useful feature. I'm also having a hard time believing that such Graphical User Interface design counts as a typical solution to a many-to-many relationship. A proof of the latter point is that you can not add new records, you can only edit one at any time, which leads me to believe the designer expects the end user to make additions directly on the tables, which is highly likely to have unfortunate consequences. I believe you must have a better example somewhere.

@MajP
The definition of a non-extensible design is an ongoing topic, in an ever-changing era of how businesses grow and adapt to new things, SQL is still the king but NoSQL is gaining a lot of traction. Would you consider one of the causes is that SQL itself is not too scalable?. Ultimately, the chosen design often responds to the priorities of both the development team and the end user, is the database going to change? probably, but when? and how often? Responding to those questions would give you a hint of what you choose to do. Consider again that the topic of this thread is "Exploring possibilities", the 90 columns do not break normalization rules and all 90 must be filled, so while it might be tedious to create 90 columns, they already belong to the record, and you do not need to add code to populate that record with children items. It is a matter of priorities, not a matter of disqualifying that option.

As for this:

Here is a good design

That's quite a statement, look at this form:

It looks like some sort of designer app, OP did not ask for a designer app, but a schema. Furthermore, the look and feel of that design is like a blast from the past. Unfortunately, it's not exactly a good thing because it doesn't meet today's high usability standards. It might be confusing or frustrating for users to navigate, and that can seriously hurt the success of your product or service. There are plenty of ways to improve that design and create a user experience that's intuitive, streamlined, and downright enjoyable without requiring a 14-pages manual, which your suggestion requires. By following modern usability principles like simplicity, consistency, and accessibility, you can make sure that your users have a smooth ride and come back for more.

 

[MajP]

If this survey is a one time use then have at it. If you have no need to do any really post survey data analysis this may suffice. The OPs problem seems trivial (one user, not reusable for other surveys) so I may be over thinking it. However, the same survey is cyclical. I am just speaking from a ton of experience in building large survey databases and often utilizing Access. Applications with multiple users and then doing heavy survey analysis on the backend. Most of these surveys include multiple response types: Likert scales, Boolean, open ended, ratings. Building a properly designed survey is an advanced exercise in normalization especially if you are surveying multiple people, same people taking multiple surveys, and planning to reuse it for other surveys. The design I referenced (talking about table structure and not the UI) supports a proper survey schema that allows for survey analysis, ability to have multiple users, multiple surveys, various types of responses.

Putting questions in separate fields does not violate normalization (in fact it may be more normalized) but will make even simple analysis very cumbersome. Adding questions, removing questions, editing questions would also be cumbersome. This is why most survey dbs that I have seen use an Entity Value Attribute model approach. But yes it is a possibility.

 

[561414]

@MajP I interpreted the OP's need was to solve only one type of survey, for multiple facilities. The design I suggested is quite the trivial thing, it's a simple many to many scheme that solves that kind of problem. Companies that verify facilities/cars/businesses according to some bill, law or norm, w/e, where the requirements are always the same can benefit from such a simple schema. If they'll use changing surveys, then a survey designer schema like the one in your referenced link is going to be solve it.

@KitaYama

I think the whole purpose of a good schema is the flexibility of the database against future changes, without the necessity in changing the design of the objects.

There's the NoSQL aspect of the conversation. If you search on the internet "sql vs nosql". You will find out SQL is for fixed/rigid schemas, if your database will change, use NoSQL. Changing a SQL schema is truly the stuff of nightmares in large apps. You can only guard yourself so much, in the end, you will have to make some serious changes in a lot of places regardless of the flexibility of your schema. However, with NoSQL, you just tell the front end "store this other different thing here, lol" and your engine will say "sure, whatever". Then you just tell your frontend to print whatever schema that record comes with. If that's not flexibility, I don't know what is, but I know SQL is not going to allow it. This is what google says:

I certainly think the entire community would benefit from knowing these little aspects outside of the box that Access represents. It's a great RAD but consider there's alternatives.

 

[Pat Hartman]

There's the NoSQL aspect of the conversation. If you search on the internet "sql vs nosql".

You've gone off the deep end with this. Although RDBMS options that support ODBC other than Jet/ACE are quite appropriate for an Access FE, "NoSQL" would be a silly choice since it would require unbound forms and not take advantage of ANY Access RAD features making the use of Access for such a project inappropriate. Better to use a more suitable development platform.

Here is a fairly simple survey Schema that will support an infinite number of surveys, an infinite number of questions (organized by group to support pagination) with an infinite number of respondents which can even take the same survey multiple times. "infinite" is of course a stretch but unrestricted within the framework of the RDBMS is perhaps a better description.

tblSurveyDef and tblSurveyQuestions define a survey. The GroupID organizes the questions to help group them for easier understanding. QuestionSeq provides a sequence within the group. I usually generate this ID using code and use increments of 10. The user can move things around by changing #40 to #85. Then there is a button to renumber for neatness and to ensure there is always an available slot if you need to move the questions around.
tblRespondant defines the person or entity completing the survey.
tblSurveyResponse connects the respondant with a particular survey and tblSurveyResponseAnswers holds the answers to the specific instince of a specific survey.

 

[561414]

@Pat Hartman

You've gone off the deep end with this. Although RDBMS options that support ODBC other than Jet/ACE are quite appropriate for an Access FE, "NoSQL" would be a silly choice since it would require unbound forms and not take advantage of ANY Access RAD features making the use of Access for such a project inappropriate. Better to use a more suitable development platform.

Pat, I never said OP should use NoSQL
I mentioned that technology because we're talking about flexible schemas. I've been talking about changes in schema as required by necessity/client/performance/etc. It's impossible to anticipate those changes.

@Pat Hartman , @Galaxiom , @KitaYama , @MajP
Are you going to tell me you have never required to make a change in your SQL schema?

EDIT:
In an attempt to get back on track:
@TraumaDoc Do you have some progress to check it out?