Consider two tables Parent and child.

  • 1B Child rows.
  • 10 Parent rows
  • Only 1K children have one parent each.

The relation here conceptually is OneToMany.

Is it better for performance reasons to consider it ManyToMany to be able to filter from association table of 1k rows instead of filtering from child table of 1B rows ?


What I mean by ManyToMany vs OneToMany. in database level using table association with two foreign keys vs using Foreign key from child to parent.

  • 3
    You probably need to elaborate on your question, you can for example create a small dbfiddle.uk and post back the url
    – Lennart
    Nov 5 at 13:48
  • 1
    Based on your mention of the keyword association, it sounds like you're referring to the Microsoft framework LINQ2SQL or possibly EntityFramework or some other similar ORM framework. In any case, you should specify that in your question and / or tags so people have context on what you're referring to. Also I think you'll find that defining the relationship as ManyToMany at the application level doesn't really affect performance but rather will just affect the way you're able to access associated entities. You should specify the relationship of what your entities really are.
    – J.D.
    Nov 5 at 19:03
  • 1
    It seems to me you are trying to optimize prematurely. You need to know more about what systems and implementations you are going to use before answering that - the relational design and normalization is not about performance, but about correctness imho. Only when you start tackling specific implementations you can consider such "lower level" stuff. For example index on a parent_id column can find the 1k non-NULLs fast even in 1B items. But it may take space. Then some systems offer partial indexes and the NULLs can be left out etc..
    – jkavalik
    Nov 5 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.