1

I have a database design in which I have aprox. 5 entities that can be filtered. Currently these tables all have their own filter table, e.g. the product table has a productfilter table and the customer has a customerfilter table.

I have done some research since performance (as in speed to query the db) is the most important quality attribute, I am wondering whether having a single filter table and connecting the entities that rely on it with junction tables is a better option?

Important side note the filters would only ever be joined from the Customer or Product entities when using the junction tables.

Edit:

1: Without junction tables

database design 1 2: With junction tables

database design 2

amin chihab
  • 11
  • 3
  • Could you post your intended database design? Do the filters span different entities (i.e. can you run a filter against products and customers in a single query)? – Martin Sep 30 '19 at 11:03
  • I added an image of the intended database design. As far as running a filter against multiple entities in a single query, I don't think this is necessary. – amin chihab Sep 30 '19 at 11:26
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Sep 30 '19 at 21:40
  • There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) – philipxy Oct 03 '19 at 08:06
  • Tables--base variables & query results--represent relation(ship)s/associations. [Queries do not have "sides" (or "links") & do not need to be "connected" to query--just mentioned.](https://stackoverflow.com/a/40329503/3404097) FKs though sometimes wrongly called relationships are constraints on what states can arise to allow integrity & optimization. Constraints need not hold or be declared or be known to query. PS Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text. – philipxy Oct 03 '19 at 08:07

0 Answers0