0

We're working on a CMS project with EF and MVC. We've Recently encountered a problem,

Please consider these tables:

  • Applications
  • Entities
  • ProductsCategories
  • Products

Relations are in this order:

Applications=>Entities=>ProductCategories=>Products

When we select a product by it's Id, always we should check if requested ProductsId is just for a specific application stored in Applications table, These is for preventing load other applications products, what is the best way to get a product for specific application id, We have two choice:

  1. Instead of define a relation between products and applications we can do joins with productsCategories,entities, and applications to find it => when we want to get products we don't want to know about entities or other tables that we should join it to access applications

  2. we can define a separate relation between products and applications and get it by simple select query

which of these is the best way and why?

Manish first thanks for your comment,Then please consider this that some of our tables does not have any relation with Entities for these tables we should define a relation with Entites to access Applications or define a separate as relation as mentioned above,For these tables we just define a relation and does not have extra work,except performance issue.still some of other tables has relations with entites so for this one defining a separat relation has extra work, At last please consider this,in fact all of tables should access 'Entities' some by separate relation and others can access from there parents actually for relation between products and entities we didn't define a separate relation because it doesn't has performance issue,But for relation between products and entities we should consider performance issue because in every request we should access Applications to check request Id is for current Application So what is your idea?

user3333426
  • 19
  • 5

1 Answers1

0

Let's look at your options

  1. Instead of defining a relationship, you can join the three tables to get the correct set of products: In this case, you won't have to make any database changes and anyway, you won't be fetching all the joined tables data, you would fetch only that data, which you have specified in your Linq Select List. But then, 3-tables join can be a little performance degrading when the number of rows will be very high at some point of time

  2. You can define a separate relationship between the two said tables: In this case you would have to change your database structure, that would mean, making changes in your Entity and Entity Model, and lot of testing. No doubt, it will mean simple code, ease of usage which is always welcome.

So you see, there is no clear answer, ultimately it depends on you and your code environment what you want to go with, as for me, I would go for creating a separate relationship between the Application and Product entity, cause that would cause a cleaner code with a little less effort. Besides as they say, "Code around your data-structure, and not the otherway around"

Manish Mishra
  • 12,163
  • 5
  • 35
  • 59