Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    27

    Business names entered differently by different users

    I have this table:

    tblStore

    with these fields:

    storeID (autonumber)
    storeName
    locationOrBranch

    and this table

    tblPurchased

    with these fields:

    purchasedID
    storeID (foreign key)
    itemDesc


    In the case of stores that have more than one location, there is a problem when two people inadvertently key the same store location differently. For example, take Harrisburg Chevron. On some of its receipts it calls itself Harrisburg Chevron, some just say Chevron at the top, and under that, Harrisburg. One person may key it into tblStore as storeName Chevron, locationoOrBranch Harrisburg. Person2 may key it as storeName Harrisburg Chevron, locationOrBranch Harrisburg. What makes this bad is that the business's name is Harrisburg Chevron.

    Is there some kind of search I could put on the control that works like the firefox awesome bar (suggests all matching letter combinations from anywhere in the word, with more frequently used ones near the top) that would suggest the already-entered name to the user?

    With an eye to accommodating user inadequacies, is the smart-search-and-suggest the best programmatic solution? I feel that making more rules and more inconvenient data entry policies should be plan B. Plan A should be to take what the user gives and programmatically help them (or force them) to make the right choice.

    (So, respectfully, please don't suggest policy changes, or such, as solutions. I am 100% aware of those and don't need recommendations on them. As I said, those measures are Plan B. Please, if you can't help with Plan A, please don't reply because I don't need any assistance with Plan B.)

    Thanks for your help.
    Last edited by CVD; 02-16-10 at 16:11.

  2. #2
    Join Date
    Apr 2008
    Posts
    27
    Anybody have any ideas?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CVD View Post
    Anybody have any ideas?
    "Please, if you can't help with Plan A, please don't reply because I don't need any assistance with Plan B."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    After they enter the first 3 letters you could do a search using wildcards to find a match. Specifically I would search for where the store name contained the first 3 letters after a space or started with the first 3 letters. The syntax in SQL-Server would be SELECT * FROM tblStore WHERE storename LIKE '% XXX%' OR storename LIKE 'XXX%'.

    The problem with this is that if you have a lot of stores it won't be fast. I would go with Plan B myself, but ......

    Oh, and this question has nothing to do with database design. You might have gotten a better answer posting under a different area.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    TBH, I don't really understand what Plan B is. So forgive me if this is Plan B....

    In short, there is a 1-many relationship for stores and locations. So should you not simply model that in your design? Implicit in your design is that ID is the PK and enforced, but that there is no natural key (you know you should always enforce a natural key right?).

    So I would split these off into two tables.

    The awesomebar bit is UI. I don't know how to do this however I imagine you would get best performance using AJAX techniques querying the table a little like Mark suggests. The question is how clever you want to get (e.g. account for distance in order to overcome typos) and what performance you require.

    Simplest might be to just have them select an existing store, with an option to add one if it does not already exist.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    After thinking about my answer above I came up with a slightly faster but harder to maintain possibility.

    You could build a new table that contains three columns. The first column would be each word in the name of a store, the second column would contain the full name of the store, and the third would contain the ID of the store. So for a store called "Oxbow Lake 2" you would have three entries in this table:

    "Oxbow", "Oxbow Lake 2", 217
    "Lake", "Oxbow Lake 2", 217
    "2", "Oxbow Lake 2", 217

    Now as they type you can search this table using the LIKE statement and bring up suggestions. But every time a store name changes you would have to delete the entries in this table and build new ones.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •