hgigf03ab08
Joined: 27 May 2013
Posts: 1437
Read: 0 topics
Warns: 0/5
Location: England
|
 Posted: Sun 2:58, 30 Jun 2013 Post subject: submitted item names and their synonyms |
|
|
submitted item names and their synonyms
Notes: error indicates a wrong spelling (eg. "Ericson"). description and picture of the item_names table are "globals" that can optionally be overridden by "local" description and picture fields of the items table (in case the store owner wants to supply a different picture for an item). common helps separate unique item names ("Jimmy Joe's Cheese Pizza" from "Cheese Pizza")
I think the bright side of this schema is:
Optimized searching Handling Synonyms: I can query the item_names item_synonyms tables using name LIKE %QUERY% and obtain the list of item_name_ids that need to be joined with the items table. (Examples of synonyms: "Sony Ericsson", "Sony Ericson", "X10", "X 10")
Autocompletion: Again, a simple query to the item_names table. I can avoid the usage of DISTINCT and it minimizes number of variations ("Sony Ericsson Xperia X10",[link widoczny dla zalogowanych], "Sony Ericsson Xperia X10", "Xperia X10, Sony Ericsson")
Overhead: When inserting an item,[link widoczny dla zalogowanych], I query item_names to see if this name already exists. If not, I create a new entry. When deleting an item, I count the number of entries with the same name. If this is the only item with that name, I delete the entry from the item_names table (just to keep things clean; accounts for possible erroneous submissions). And updating is the combination of both.
Weird Item Names: Store owners sometimes use sentences like "Harry Potter 1, 2 Books + CDs + Magic Hat". There's something off about having so much overhead to accommodate cases like this. This would perhaps be the prime reason I'm tempted to go for a schema like this:
items: id name picture price description picture
(. with item_names and item_synonyms as utility tables that I could query)
Is there a better schema you would suggested?
Should item names be normalized for autocomplete? Is this probably what Facebook does for "School", "City" entries?
Is the first schema or the second better/optimal for search?
I think the problem is that we unclear of your REQUIREMENTS. I going to suggest what I think is happening. You equivalent to Amazon. More than one Seller could offer {Nike Air Jordon Red/White 10.5US},[link widoczny dla zalogowanych]. But they can all call them by different names so you have a normalization problem,[link widoczny dla zalogowanych]. These aren SKU items that do have a universal PK,[link widoczny dla zalogowanych]. So you trying to derive that two things are really the same thing by a comparison of characters in the name? And you think this is an issue of the right schema? I don get it. Stephanie Page Jan 6 '11 at 18:47
The requirements you state in your comment ("Optimized searching", "Handling Synonyms" and "Autocomplete") are not things that are generally associated with an RDBMS. It sounds like what you're trying to solve is a searching problem,[link widoczny dla zalogowanych], not a data storage and normalization problem. You might want to start looking at some search architectures like Solr
相关的主题文章:
[link widoczny dla zalogowanych]
[link widoczny dla zalogowanych]
The post has been approved 0 times
|
|
