问题描述:

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:

Entries table

FIELD NAME      DATA TYPE   ATTRIBUTES
EntryID         AutoNumber  PK; required
EntryName       Text        Unique; required

Options table

FIELD NAME      DATA TYPE   ATTRIBUTES
OptionID        AutoNumber  PK; required
OptionName      Text        Unique; required

EntryOptions table

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

EntryOptionText table

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).

Foreign keys

As you may well know, these are set up either using the wizard or in the Relationships window in Access:

  • Entries will have a one-to-many relation with EntryOptions
  • Options will have a one-to-many relation with EntryOptions
  • EntryOptions will have a one-to-one relation with EntryOptionsText

Personally I find EntryOptionsText overkill though - I'd just have OptionsText as a nullable field in EntryOptions.

Unique indices

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 EntryID and OptionID, this will be the solution for your 'additional kink', preventing multiple records with the same EntryID/OptionID combination.

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).

相关阅读:
Top