Checkboxes and Normalization

[mgrabows]

We're creating a database for a rural development project in the Philippines. The database tracks the accreditations of our medical facilities and in turn determines what equipment and quantities they must maintain for their accreditations. We have a functioning structure now, but we're concerned that it might not be normalized properly because of the way were using checkboxes to track accreditations. We've spent the past few days researching normalization on the Web, but we're still not certain if changing our structure is necessary. Any advice would be a big help.

We currently have three tables for the system.

tblFacilities: stores facility information and accreditations. NOTE: facilities can have a mix of accreditation
facilityID (PK)
facilityName
facilityZone
facilityType
projectedPopulation
Primary (this is an accreditation indicator, controlled by a checkbox)
Secondary (accreditation indicator, controlled by a checkbox)
Tertiary (accreditation indicator, controlled by a checkbox)

tblEquipAttributes: master equipment list
equipID (PK)
equipName
equipReqUnits
equipPrimary (checkbox, if the equipment is for Primary accreditation)
equipSecondary (checkbox, if the equipment is for Secondary accreditation)
equipTertiary (checkbox, if the equipment is for Tertiary accreditation)

tblEquipInventory: stores all physical equipment at the facilities
ID (autonumber, PK)
facilityID (FK)
equipID (FK)
equipFuncUnits (functional units)
equipAcqDate (acquisition date)

Our concern is the checkbox scheme. Since the accreditations are rather static (i.e. new accreditation types are rare, although facilities might assume different accreditations in the future), we used that instead of a lookup table initially. However, the checkboxes have made query writing a bit more tricky than we think it should be. It works, but it just doesn't feel normalized properly.

Would it be more appropriate to pull the accreditation indicators out of tblFacilities and store them in something like this?

tblFacilityAccreds:
facilityID (FK)
accreditationID (lookup)
accreditationName (lookup)

The table would grow vertically with several entries for facilities with multiple accreditations.

 

[georgedwilkinson]

You're right, it is not properly normalized.

Think about it in a business sense. The word "Primary" is not a business word, nor is Secondary or Tertiary. As such, they should not be used for database object names.

An example of how to straighten this out is to create a couple of more tables and move some of the columns from the existing tables:
tblFacilities: stores facility information. NOTE: facilities can have a mix of accreditation
ID (Autonumber PK)
Name
Zone (this might be a FK from another table?)
Type (most likely a FK from another table)
ProjectedPopulation
(Adding the word "Facility" to the beginning of each column name is redundant...The table is already named "Facility")

AccreditationTypes: Stores the types of accreditations that are available for Facilities, etc.
ID (Autonumber PK)
AccreditationType (text human readable Accreditation Type)
Description (text long description)
(Other columns that help manage design time issues)

FacilityAccreditations: Stores information about the accreditations earned by each facility.
ID (Autonumber PK - just in case)
Facility (FK Long Integer)
AccreditationType (FK Long Integer)
AccreditationDate (date/time)
ExpirationDate (date/time)
Description (text)
Other columns related to the event of the accreditation or to manage design time issues.

Then do the same thing with your other un-normalized tables.

 

[mgrabows]

Thanks for your response! It helps us know that we're on the right track to ironing out our structure. Many thanks.

Just one question based on your advice.

Our instinct is to avoid autonumbering tables that contain static information. For example, the facilities will not change. We have 33 and in 10 years there will probably still be 33. So we'd rather give them their own IDs and use that as a primary key. Is there a reason against doing this?

Thanks again for your advice!

 

[georgedwilkinson]

You can use whatever you want for a PK. However, I've found it easier just to give everything an autonumber PK. It allows for changes there is no way you could have anticipated.

Whatever you use, you should probably make sure it is numeric. If tables you make joins to become large, text keys will slow down your queries.

 

[mgrabows]

The only issue we have now is moving away from our previous, checkbox-centric, input forms (Access 2003) for the facility accreditations.

I know this is a topic for another forum, but I thought since my case is already here, I might ask you once again for advice. After a day of trying, I feel like we might be foiled in an attempt for something as simple as we had before using the checkboxes.

We've got a form with our basic facility information, then a subform where the accreditations will be entered.

We've considered (1) combo boxes, but we'd have to provide 12 boxes in case a facility has all accreditations. Not practical. (2) We tried to make a continuous form work, but couldn't do it without an unbound field, which ruins everything. (3) We're kicking around ideas of unbound checkboxes that would, through some coding, modify the FacilityAccreditations table.

None of them seem solid or pretty. Any simple approaches we might be overlooking?

Salamat!

 

[DCrake]

In your current form layout you are using checkboxes to denote the accreditation type if you chang ethis to an option group. Thus preventing more than one accreditation type being selected this should not mess up your form layout.

How to update you current records:

Lets say we give the values 1, 2 & 3 for the Primary, Secondary and Tertiary accredition types.

In your revised Factity table you will have a new field call AcredID

In a query code:

Acred:IIF(Primary=True,1,IIF(Secondary=True,2,3))

Bring down the new field and the three types and view the results, You should see either a 1, 2 or a 3 in the Acred column. Compare this against the other three to see if they match up. If they do, Highlight the Acred column and select copy, nect highlight the blank Acred column and select paste.

You new field will now have the correct code in the field.

Finally for the new option group on your form, bind this to the new ACredID field.

This will then complete the transition form old to new. If you have any issues get back to me.

David

 

[mgrabows]

In your current form layout you are using checkboxes to denote the accreditation type if you chang ethis to an option group. Thus preventing more than one accreditation type being selected this should not mess up your form layout.

The problem we run into is that facilities can have multiple accreditations. I might be missing something in your explanation, but I think the allowance of multiple accreditations means we'll have to come up with another approach.

We're trying to normalize our structure by separating basic facility information (i.e. name, location, zone) from the facility's accreditation. That means we have a lookup table for the accreditations and codes, and another table that stores the facility's accreditations. It looks like this:

accreditationCodes: the lookup
code (PK)
accreditationName
description

facilityAccreditations: the table that stores accreditations per row
ID (autonumber PK)
facilityID (FK)
accreditationCode (FK)

facilities: basic facility information
ID (PK)
name
zone

The facility input form displays the basic facility information and now we're working on a subform that will update the accreditations in facilityAccreditations. The simpler, the better of course, because the project will be left in limited technical hands.

The approach were now working on is a subform that displays facilityAccreditations as a datasheet. To add to or edit the facilityAccreditations, the user will click a button that will bring up a list of 12 combo boxes, allowing a facility to have a multiple accreditations, if not all 12.

 

[The_Doc_Man]

In the final analysis, form must follow function in Access. I.e. you decide what you need and then program it. But your problem is compounded by a modeling analysis. You must decide what entities / attributes / concepts to track - and THEN do some tracking.

By saying that a site might have multiple accreditations, you essentially guarantee that all such accreditations must be in a table that is linked to but different from the list of sites. Then you link an accreditation to the site by the site's number. Use the option group to identify which kind (primary, secondary, tertiary) of accreditation.

You are showing a three-layered relationship: Site - Accreditation entry - Accreditation translation. You would do this with a query-within-a-query. However, you claim to be foundering on this point. Perhaps it is because it is not clear to us as to how you become accredited. Is it simply a matter of having some particular number/type of facility objects (beds, autoclaves, gurneys, etc.) that qualifies you? Is it staffing that turns the trick? Your description of your difficulty indicates to me that you have not fully attacked that part of the problem. We can't make more constructive suggestions unless we know more about how one gets accredited.

Stated another way, this is an old programmer's rule: Access won't do on a computer anything you couldn't do on paper. (Translation: before trying to use Access, be certain that you fully understand your own problem analysis.)

 

[dportas]

Assuming that equipPrimary, equipSecondary and equipTertiary are not nullable and are functionally determined by the key(s) of the table then from a Normalization perspective there is nothing wrong with the design. It may or may not be a good idea to change the design for other unrelated reasons however.

 

[The_Doc_Man]

dportas is absolutely right. Normalization doesn't guarantee correctness, just as denormalization doesn't guarantee incorrectness (but does guarantee awkwardness).

What must always be the final source of deciding correct or incorrect is whether your design supports your business model. And to decide that, you always, always, always start from the business model, working your way from gross detail to fine detail. At each step of the way, you decide what the business model is doing AND THEN decide what you need in Access to properly model that business process. Doing it any other way is a case of the tail wagging the dog.

 

[mgrabows]

Thank you Doc Man and dportas both for your comments.

We did accomplish a design that was better suited for our organization here. As dportas mentioned, our initial usage of checkboxes to indicate accreditations was not necessarily unnormalized, but rather just kind of a pain to manage in MS Access. Sustainability is key (especially since I'll be leaving) so now we use one table (facilityAccreditations) to list all accreditations for all facilities.

Reading Allen Browne's article (http://allenbrowne.com/casu-23.html) along with everything here, helped us understand why we should avoid checkboxes and how to get around them.

This was our final table design:

accredCodes (lookup):
code (autonumber, PK)
name
description

facilityAccreditations:
ID (autonumber, PK)
facilityID (FK)
code
name

facilities:
facilityID (PK)
facilityName
facilityZone
facilityType

equipAttributes:
equipID (PK)
accredCode (FK)
name
reqUnits, etc

inventory:
ID (autonumber, PK)
facilityID (FK)
equipID (FK)
equipAccredCode (FK)
funcUnits
nonFuncUnits, etc

This allows us to query a facility's accreditations and build a list of all equipment they need. That list is appended to the inventory table. When they log into the input form, a global variable passes their facilityID to the form's recordset which queries the inventory table for all rows matching their ID.

It's working great now. Thanks for all the help and explanations! They were a big help.

Salamat!