Normalization for checkboxes?

[yepwingtim]

I have 30 fields checkboxes

Is it possible to normalize the data to a single combobox but display it in the form as checkboxes

Note: the backend is mysql I guess that's why I have number as data type

________
Vapir No2 Vaporizer

 

[stopher]

You could use a list box which allows you to select multiple items. Or you could create a subform.

Chris

 

[gemma-the-husky]

why do you have number as a data type - you can still have yes/no as a data type?

===================
I dont really see the point of combining separate flags- I dont see this as normalising data, as you put it -

however, one way to combine multiple (8) yes/no flags is to store them in a single 8-bit feild (byte), and then use bitwise operations to test them.

so given a binary number that looks like

01101010 (106 in decimal, I think, but that doesnt matter) - the bit flags indicate that the second, third, fifth and seventh flags are all on (set to 1)

then the individual settings correspond to
bit 1 = 128
bit 2 = 64
bit 3 = 32
bit 4 = 16
bit 5 = 8
bit 6 = 4
bit 7 = 2
bit 8 = 1

you can use various bit wise operations to test settings

eg

if mynumber AND 16 = 16 then the flag at bit posiiton 4 is set
in this case the expression mynumber AND 16 returns 0, indicating the flag is NOT set.


you also have the very useful xor which will toggle a setting

so

mynumber xor 16, toggles the setting for the "16" bit and then
mynumber xor 16, returns it to its original state

xor is commonly used in encryption routines.

one benefit of all this is that you use one byte for 8 flags, instead of a whole byte for each flag, so the data storage is more compact.


===============
the problem with 30 bits (yes/no flags) is that you have 2^30 possibilities - ie combinations of yes/no flags in total

since 2^10 is 1024 (1000 approx), then 2^20 is 1,000,000 and 2^30 is 1 billion - which is surely an unreasonable number of possibilities to consider within a single drop down!

 

[Galaxiom]

I am not really clear on the question but thought to explain what I think I understand of Stopher's post.

You currently have many fields each record.

In the alternative structure the main table holds common appointment information such as say, the date, doctor and location.

The procedures themselves are each held as separate records in a related table. This table has two fields. AppointmentID and ProcedureID.

ProcedureID comes from a Procedures table which has fields: ProcedureID, ProcedureName and fields other aspects common to the procedure. It is related by ProcedureID allowing the ProcedureName to be displayed.

This table is also used as the Row Source for the ProcedureID List Box.

This structure allows new procedures to be added as new records in the Procedures table. The original structure requires the addition of a new field which is far more complex.

The appplied procedures can be displayed in a subform. Normally only the active proceudres are recorded. The absence of a record for the procedure indicated the same as the unchecked box.

It is possible to make the form still look like a bunch of checkboxes but it is more complex than simply adding new records to the subform for selected procedures.

 

[The_Doc_Man]

This is an "Attributes" discussion. There are two ways to handle this.

The simpler way is to have a BOOLEAN data type for each yes/no attribute and store that in a single record. One record, several fields.

The more complex way would be to make a list of possible attributes with code numbers, then make a parent/child table that is sparsely populated. Any flag would be TRUE if a child record exists for it. You make it false by deleting the child record for that attribute. The child table MIGHT be as simple as two integers, the parent record number as a foreign key and the code number (from a translation table, for example) as a foreign key. That is the ultimate and pure JUNCTION table.

But let's look at normalization (your original comment). If you have 30 yes/no attributes for each primary thing you are tracking, the question is whether you need to "normalize" the 30 fields tha hold your 30 yes/no values. This does not matter whether we are talking 30 byte-integers, 30 yes/no fields, or 30 "packed" bit codes using binary AND, OR, and XOR operators, it is the same question.

I think that in this case, I would vote for the inclusion of 30 yes/no fields in the single record rather than the multiple child records case. Here, you have to ask the question about whether you would be violating the rule about "the values in the record must be uniquely relevant to the entity selected by the primary key." I don't think this would be such a violation.

Trying to separate out the attributes would be possible, I suppose, but let's put it in a different perspective. Suppose this was a simple address database where you stored first and last names separately. If you had two people named Smith, would you want to have a table of possible last names and store the index to the correct name? Or if you had two guys named Richard. Would you store an index to the correct first name? No, in both cases you would store the literal value, ignoring the potential for duplicated value in one or both name fields.

Therefore, to my considered opinion, you probably should not bother to try to normalize that aspect of your records any farther. From a practical viewpoint, you can save space by compacting the field into a binary item. Because of the overhead of JUNCTION tables even when you make them SPARSE, it is almost unthinkable that you would save much space by normalizing when compared to creating the JUNCTION tables.

I know this went far afield, but I was attempting to address your issues to normalize or not to normalize... THAT was the question.

 

[KenHigg]

My answer would be yes you can store these 30 odd bits if info in one field but you will need to have a routine that parses and concatenates each time this portion of the record is displayed, you won't be able to simply display the record and expect it to look like what you have in your screen shot.

And one other thought - Do you need to design this so that you may have to edit the list? If someday they want to add another option or maybe disable one, then this would be a major shift in your model.

 

[vbaInet]

In my opinion the main questions here are

1. Is the list is going to increase?
2. If it is, how frequent will the additions be?
3. Finally, who will be responsible for updating the list? Users or just the Administrator.

Seeing what kind of list it is I can make my own conclusions, which are, the list would probably not change and even if it does maybe once a year (after Senior Managers decide they want to store more specific information about a patient). I can also infer that the database admin would be responsible for any changes to the list.

Despite the fact that it may not change frequently, it might be easier to maintain using a junction table (as has already been mentioned). Some of the attributes may become inactive after a few months and if that happened and we kept everything as checkboxes, this would certainly require a new front end. In fact, an addition would also require a new front end. Of course, one might argue that we can overcome this by dropping lots of checkboxes and setting it's visible property in code. Waste of time really!

To display the attributes I would opt for a listview control with checkboxes.

I noticed your checkboxes are grouped into five sections. In this case, I would also categorise these attributes so that users can refine or streamline their searches using these categories. An extra field in your junction table for the categorisation would do. This can obviously be normalised further but I don't think you would get much performance benefit.

Just my thoughts on this