I've created a database to store information for Design work my company does. The main table currently has a lot of Null fields in it, because there are numerous "optional" fields.... a prime candidate for normalization, as I understand it!
My issues more arise from understanding of structure. I'll do my best to explain my problem:
In the main table, there are Entries (my PK) and a number of grouped options (A Y/N and a Memo). The text is optional, though only if the related Y/N is set to "Yes". A quick example is as follows (did a quick mockup in Excel):
Keep in mind, there are many more options than just these (and MANY more nulls). I'd like to normalize these out into 2 new tables, but the structure is simply beyond my skill level. In theory it would look something like this:
This would separate out the Y/N and the text, relate the two via the original option number, and get rid of ALL of my nulls.
Structurally, though, I have no idea how to design everything. I figure I need a junction table somewhere, but the standard tutorial example of "Orders and Products" doesn't fit cleanly into what I'm trying to do (or at least, I can't see it)
An additional kink, I need to only accept records for "OptionB" if there exists a "Yes" in the corresponding "OptionA". I was able to set this up using a validation rule in the original table, but I'm not sure if I can use the same method when the fields are split up.
I'm also at a loss as to how to set my keys for the second two tables - both "Entry" and "Option" need to be able to have duplicates, but I need to stop duplicates of unique combinations of those two fields (there should only be one record with "Entry 1|Option 1")
That was wordy and I hope it makes sense... I'd be more than happy to provide any further clarification.
You've got most of the way there yourself. Assuming the options are all independent (i.e., one doesn't become settable only when another has been), and further, the status of an option is either 'yes' or 'no' not potentially 'unknown' (as distinct from 'no') instead, you could do this:
FIELD NAME DATA TYPE ATTRIBUTES EntryID AutoNumber PK; required EntryName Text Unique; required
FIELD NAME DATA TYPE ATTRIBUTES OptionID AutoNumber PK; required OptionName Text Unique; required
FIELD NAME DATA TYPE ATTRIBUTES EntryOptionID AutoNumber PK; required EntryID Number FK to Entries.EntryID: required OptionID Number FK to Options.OptionID; required Unique index combining EntryID and OptionID
FIELD NAME DATA TYPE ATTRIBUTES EntryOptionID Number PK; FK to EntryOptions.EntryOptionID; required OptionText Text Required
I wasn’t sure whether your ‘Option 1’, ‘Option 2’ etc. were just markers, but either way, it can be prudent to set up a ‘display name’ field anyhow given an AutoNumber field only looks like a counter (as soon as you start deleting records in the middle it becomes discontinuous).
As you may well know, these are set up either using the wizard or in the Relationships window in Access:
Entrieswill have a one-to-many relation with
Optionswill have a one-to-many relation with
EntryOptionswill have a one-to-one relation with
Personally I find
EntryOptionsText overkill though - I'd just have
OptionsText as a nullable field in
The rationale for creating a unique index on each display name field is hopefully pretty obvious. With respect to the one I suggest against
EntryOptions, which is to cover both
OptionID, this will be the solution for your 'additional kink', preventing multiple records with the same
Setting up the single-field indices is trivial, as you have the ‘Indexed’ field property in front of you when designing the table (set it to ’Yes (no Duplicates)’). For the index covering two fields go to the Design ribbon tab, select Show/Hide > Indexes, and create the index explicitly (see https://support.office.com/en-gb/article/Create-and-use-an-index-to-improve-performance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce).