A lot of Many To Manys

Question

Hi All,

I am working on a DB Structure and it seems like I have ended up with a lot of many to many tables. The reason I have am getting so many of them is that my base table is items and each item can have multiple occurrences of the search criteria that will be surfaced to the users. So I normalized all of the search criteria and used many to many tables to link the items and search criteria. For whatever reason it just doesn't feel right to have 7 search criteria tables and 7 many to manys.

Are there better ways to formulate these relationships and still hold to 3rd normal form?

As always greatly appreciate the input.

--S

Answer 1

I suspect you would be better served by a star schema and denormalized dimension tables. Your base table would act as the fact table. Your search criteria would be rotated out into multiple columns and placed in one or more dimension tables.

eg, if attribute A is wholly dependent on attribute B, and attribute B is wholly dependent on attribute C, you might make a dimension table with (C, B, A), and then migrate C to your base table as a foreign key. Repeat for all related sets of attributes.

If you have some odd-ball low-cardinality attributes that are not clearly related but do cluster together, you might make another dimension table out of their cross-product, tack on a primary key, and migrate this key to the base table as well.

If 3rd normal form is required (and it should only be required if data is being updated by multiple processes), then you could normalize the dimension tables into what are called snow-flake dimensions. You'll pay a cost for this, as every query with require that many more joins.

Answer 2

I'm not really sure if i understood your model.. i believe that you have a Item table and 7 other tables related to it (let's say, category, author, country, etc, etc). And your search has to look up those 7 NxN relations, and you don't like a query that makes 7 joins... if that's the problem, then you should think about using full text search.