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')
Somewhat unintuitively you should drop the reference to course and just leave the references to user and topic in the Undergoing table. To answer directly your question: there should be only two participating entities so it is not a ternary relationship.
This is one of those interesting cases in normalization where Third normal form (3NF) is fulfilled but Boyce–Codd normal form (BCNF) is not. Both are desirable forms in normal database formalization. What breaks the BCNF is the dependency topic_id->course_id. In plain English: if you know the topic_id you can deduce the course_id also so you don't need an explicit reference.
When you have user_topics relation with references only to user_id and topic_id you can easily fetch the courses of a user with a regular JOIN (via module to course).
Here is more formally put the dependency that is allowed in 3NF but not in BCNF (Third normal form - Wikipedia):
Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)
Boyce–Codd normal form (Wikipedia):
Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF. Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF.
Best Answer
Answers
Exposition
The rules you give are all binary rules. They relate one entity type to another. If you have a rule which mentions three entity type then a ternary table would be appropriate, but you have not. For example the intersection entity type "TargetMarket" would be ternary - Red Bull (company) targets energy drink (product) to software (sector).
I'm inferring from the many-to-many between company and product that the products are generic. For example "chocolate" and "spreadsheet". They cannot be "Toblerone" and "MS Excel" as they are trademarked and can be produced by only one company (ignoring licencing agreements). If all companies stoped producing chocolate (God forbid!) I imagine you would still like to record that "chocolate" was in the sector "food". With a ternary table this would not be possible. If all companies ceased producing chocolate (i.e. deleted corresponding rows from the ternary table) the chocolate <-> food association would disappear. Similarly a newly-formed company could not be recorded in this system until it was producing products. Should it ever cease producing products (e.g. become a shell company, go into administration) it would have to be removed from the ternary table and hence from the system entirely.
As I mentioned in the parent question, the full answer depends on the meaning of the relationships between the entity types, as embedded in their names, and both binary and ternary tables may be required. Say a company produces 4 products, each of which is categorised in 3 sectors. That would produce 12 possible sector-product-company combinations. If your system requires to capture that only, say, 9 of these possibilities are valid or exist in fact, or some other well-named constraint then the ternary table would be the right way to do this. But this is a rule which is not mentioned in your set above.
I would suggest you examine the constraints between a company's sector and those of the products it produces. There may be redundancies there which should be removed for the model.