Modeling a db that has products: What are the good practices/solutions? How to treat grammar variations in the words

database-designdatabase-diagramsfull-text-search

The problem is:

  • A Company belongs to a sector.
  • A Company produces products.
  • A Sector contains products.
  • A Product belongs to sectors.

Three entities: Company, Sector and Product. And it's a ternary relationship, right?

The dilemma is:

Possibility 1

I follow the above modeling. But I don't know how much hassle I'll get in the implementation and in the day-to-day use of the database. I'm saying that because the user could register the product "chair", but also "chairs", but also "wood chairs", etc…

How would I treat that singular-plural-adjective-grammar-etc trouble, under that modeling?

How would I guarantee to the user that no matter how he types "chair" (singular, plural, with or without adjectives), he will get all the results that the database can provide?

Possibility 2

I do not follow the modeling. I skip the Product entity, and just make an attribute "products" in the parent entity, which is Company. That attribute would be a big string containing all the products.

Example: The company 'Wood Corporation' has the attribute "products" equal to "chair chairs woods wood metal metals …" (space-separated values). It looks a very ugly solution and very prone to bad data input by the user.

What do you think? What are the common practices/solutions? How to treat the grammar variations?

Maybe I'm just looking too far forward (tables, implementation, use), because I'm just in the modeling phase. Yet, those concerns keep arising, I can't avoid it and it bites my neurons.

Best Answer

How would I guarantee to the user that no matter how he types "chair" (singular, plural, with or without adjectives), he will get all the results that the database can provide?

Wildcard queries could solve your immediate problem. For example, your products table may have the following entries:

productID    companyID    productType
--------------------------------------
1            1            chair
2            2            wooden chair
3            2            desk chairs

If you wanted to get all of the products with the word chair in it, you could run the following query (mysql):

select productID, productType
from products
where productType like '%chair%'

However, that design isn't optimal, because you will encounter a problem when a user types in recliner, or some other type of chair that doesn't explicitly have the word "chair" in it.

A better design would be to have a separate table that specifies the categories of products. For example, you could have a categories table that contained a fixed set of categories:

categories table

categoryID    categoryName
---------------------------
1             chair
2             office furniture
3             wooden furniture

Then, you would have a table to map categories to products. Products can have many categories, which will give your users more flexibility when searching. Here is the productCategories table.

productCategories table

categoryID    productID
-----------------------
1             1
1             2
1             3
1             4
2             3
3             2

Finally, you would have your normal products table, which would contain additional information about your products.

Products table

productID    companyID    productName              price
---------------------------------------------------------------
1            2            Plain chair              40.00
2            2            Modern wood chair        120.00
3            2            Black office chair       160.00
4            3            Leather recliner         800.00

With this model, finding all of the chairs is simple. You just need find all products assigned to the chair category.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName = 'chair'

This would return the recliner as well. Also, this model gives the user some flexibility for creating more specific searches. For example we can change only one line (see the last line of the query below) to fetch all of the wooden chairs.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName in ('chair', 'wooden furniture')
Related Question