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:
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?
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.