开发者

Access Query - Compare Multiple User Selections Against Each Other

I'm running into a conceptual problem that I cannot seem to conquer in my mind.

Let's say I want a user to enter what they're currently wearing into a database via a form. Throwing 'T-Shirt' and 'Blue' in a new row is incredibly easy. However, let's say I want to compare one users against others, and rank in order from most similar to least.

This becomes a huge ni开发者_运维技巧ghtmare when you consider the amount of options available.

  • Undershirt
  • Overshirt
  • Jacket
  • Scarf/Necklaces
  • Headwear
  • Pants
  • Underwear
  • Leggings
  • Socks
  • Footwear
  • Accessories

As I see it, I could hard-code in the 11 categories above and let a user make selections from drop-drop boxes tailored to each category. Now, let's use an example of 'Undershirt' and 'Overshirt'. Depending on the person, a long-sleeved shirt could be used as either; they're still wearing one. If I make users put values in categories, User A might put it in one and User B might but it in another category. And they wouldn't get compared because of that, separate categories.

Now, instead of hard-coding in categories (and thus making a limit of how much a user can enter), I could put each item into its own row and search by User ID. But let's say a person enters in shorts one day, and next throws in jeans and a shirt. How can I make sure that they're compared separately (e.g., dress compared to shorts, dress compared to jeans+shirt) and not (dress compared to shorts+jeans+shirt).

As to actually comparing, each item vs. each other could be performed via a 2d lookup table. (Row Dress vs. Column Jeans would net a zero, Row Dress vs. Column Dress would net a one)


The appropriate design for this would depend on the acceptable margin of error. If there is zero acceptable error, then you must present the users with the categories and they specify true/false yes/no for each one or select from a limited set of possible answers.

      HANDS:
             gloves
             mittens
             brass knuckles

             [Caveat: user could be wearing brass knuckles inside the mittens. You have to take into account
             whether values are mutually exclusive or not. Barefoot <> no socks.
              Someone who is barefoot is not wearing socks but someone not wearings socks may be wearing docksiders]


      FEET1: 
             anklet socks
             sheer stockings
             fishnet stockings
             ragg wool hiking socks
             kneesocks
             gym socks
             no socks


      FEET2: 
           mocassins
           running shoes
           sandals 
           wing-tips
           uggs
           spike heels               
           ...


      HEAD: 
            sombrero
            beret
            baseball hat
            pirate's hat
            beanie
            knitted cap

      NECK:
            scarf
            mock turtleneck aka dickie

Et cetera et cetera ad nauseam.

Or if margin of error is very generous, you could allow simple freeform text-entry and match/partial-match on words. Slightly less error : you could set up a synonyms table and match on the synonyms of the supplied words.


As a general rule, get the database design right and worry about reporting later. If this is not just a thought exercise, you may like to say what you are actually comparing, because with the above, a person is quite likely to say "tuxedo" or "evening dress", and let the details be inferred, whereas in some other area, this may not be possible. Even so, it seems that you would need a minimum of three columns (fields) for each item:

Timestamp
Major category (jeans, trousers, skirt)
Item (Levi's, tweeds, mini)

If accuracy is particularly important, you will need a trained interviewer :)

I have just noticed underwear in that list, which is even more complicated, because what would qualify as full underwear for a lady of a certain age is by no means the same as that for a gentleman of ten years.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜