0

I have a Product entity and table and would like the database design to allow finding a product by different keywords on top of its name, that is, like using a thesaurus e.g. product name "HDR-TD20V" should also be found by keywords "camcorder", "camera", "video camera", etc. Note that this same mechanics can be used to locate the same record from different input languages e.g. looking for "camara de video" (Spanish) or "videokamera" (German) should also find the same record.

Assuming that I am using Hibernate-search i.e. Lucene I have the following two design choices:

  1. De-normalized approach: Product table has a keywords column that contain comma separated keywords for that product. This clearly violates the First Normal Form "... the value of each attribute contains only a single value from that domain.". However, this would integrate nicely with Hibernate-search.
  2. Normalized approach: Define a Keyword entity table i.e. Keyword(id,keyword,languageId) and the many-to-many association ProductKeyword(productId,keywordId) but the integration with Hibernate-Search is not so intuitive anymore ... unless e.g. I create a materialized view i.e. select * from Product p, Keyword k, ProductKeyword pk where p.id=pk.productId and k.id=pk.keywordId and index this materialized view.

I would of course prefer the choice 2 but I am not sure how Hibernate-search would optimally cover this use-case.

SkyWalker
  • 13,729
  • 18
  • 91
  • 187
  • 1
    Are you sure it's the product keyword and not the product category keyword? – Neil McGuigan Feb 04 '13 at 23:14
  • I am sure, I used probably a bad example because indeed camcorder is a category. I will support (hierarchical) categories separately. It is a different aspect altogether. But thank you. – SkyWalker Feb 04 '13 at 23:19
  • see http://stackoverflow.com/questions/316780/schema-for-a-multilanguage-database and http://stackoverflow.com/questions/929410/what-are-best-practices-for-multi-language-database-design – Neil McGuigan Feb 04 '13 at 23:26
  • What is in your opinion the problem with approach #2 in Hibernate Search. Hibernate Search can index associations, either via @IndexEmbedded or via a custom _FieldBridge_. I don't understand why you need a view here. Maybe you could outline your actual entities. – Hardy Feb 05 '13 at 09:03
  • @Hardy indexing a many-to-many association is actually the OP core question, can u add an answer elaborating how to do that in Hibernate-Search and I will accept it. – SkyWalker Feb 06 '13 at 09:21

1 Answers1

1

Something like this should work:

@Indexed
public class Product {
    @Id
    private long id;

    @ManyToMany
    @IndexedEmbedded
    Set<Keyword> keywords;

    // ...
}

public class Keyword {

    @Id
    private long id;

    // only needed if you want a bidirectional relation
    @ManyToMany
    @ContainedIn
    Set<Product> products;

    // ...
}

I am leaving out options for lazy loading etc. How exactly the JPA mapping looks like depends on the user case

Hardy
  • 18,659
  • 3
  • 49
  • 65
  • Thank you! IndexedEmbedded is indeed the answer to what I was looking for. Therefore choice #2 fits perfectly with Hibernate-Search. – SkyWalker Feb 07 '13 at 14:04