Making Checkboxes for Categories

[mae]

I am trying to make a very simple database for technical notes. I want to be
able to assign each note to multiple categories or keywords that I can filter
on. For example, some categories might be Configuration, Installation,
Billing, MS Word, MS Excel, Record Keeping, Documentation, etc. If my note
is, "MS Word costs $100" it would have category Billing and category MS Word.
I have a Categories table, but I'm not sure how I would set up the Notes
table. The only thing that's coming to mind is a bunch of yes/no fields, but
that can't be right. ... Can it?

Thands for any advice

 

[Allen Browne]

Your hunch that checkboxes would be the wrong approach is spot on.

You have 2 tables:
1. Notes table, with a NoteID primary key.
2. Keyword table with a KeywordID primary key (which can be Text - i.e. the
actual keyword - if you like.)

You need a 3rd table with just 2 fields:
NoteID which note this entry is for.
KeywordID what keyword applies to this note.
So if Note 66 has 5 keywords, it has 5 records in this table.

The interface is a main table bound to your Notes table, with a subform
bound to the 3rd table. The subform has a combo for selecting the keyword,
and you can select as many keywords as you like, one per row, in the
subform.

 

[Ken Sheridan]

Multiple Boolean (Yes/No) columns would most definitely not be the right way
to go! Its what's known in the jargon as 'encoding data as column headings'.
Data must only be stored as values at column positions in rows in tables.

You firstly need a table Categories with a Category column (the primary key)
and one row per category. Then you need a Notes table with a Note text field
and a unique primary key column, for which you can use an autonumber, NoteID
say.

Because each note can be related to one or more category and each category
can be related to one or more notes, what you have is a many-to-many
relationship between them. This type of relationship is modelled by a third
table NotesCategories say. This has two columns NoteID (not an autonumber in
this case but a straightforward long integer number data type) and Category
(again a text field). These are foreign keys referencing the primary keys
of categories and Notes. So if Note 1 is in 3 categories there would be
three rows in the NotesCategories table with 1 in the NoteID column and the 3
categories in the category column.

For data entry you'd use a form bound to the Notes table with a subform
bound to the NotesCategories table embedded in it and linked on the Category
columns. In the subform, which should be in continuous form view, have just
one control, a combo box bound to the Category column. The note is entered
in the main parent form, and one row per category for that note is entered in
the subform simply by selecting from the combo box's list. You can this have
as few or as many categories per note simply by selecting a category in a new
row in the subform for each.

You might have noticed that the Notes table uses a 'surrogate' numeric
primary key, while the Categories column uses a 'natural' primary key. The
latter could have used a surrogate key, but as each category value is unique
a natural key is fine here. A surrogate key is necessary where the values of
other columns might not necessarily be unique, which I imagine is unlikely in
your case, but perhaps theoretically possible. Nevertheless long text
expressions don't make particularly good keys, so a surrogate key would be
the best bet here in any case. Never use personal names as a key BTW; they
can be duplicated – I once worked with two Maggie Taylors!

Ken Sheridan
Stafford, England

 

[Jeff L]

Yes/No fields for what?

I would create your notes table (NoteID as PK AutoNumber, CategoryID,
Note).

OR

You could also create a "link" table would would serve as a go between
for Category and Notes. The fields would be CategoryID, NoteID and you
would link the two tables together through this table. This method is
a little more complicated, but not too bad.

Hope that helps!

 

[Ken Sheridan]

Jeff:

It has to be the second method in fact. The relationship type is
many-to-many so must be modelled by a third table. Two tables would involve
extensive redundancy as each note would have to be repeated for every
category to which it is assigned.

Ken Sheridan
Stafford, England