Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL AntiPattern - A lot of Join

 
Mark Reyes
Ranch Hand
Posts: 426
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have been working on this notion for some time but I would like to ask you about this.

Most of the table structure that I am working does not come with naturally occurring key so we have to create a makeup key for this.

Tables are normalize but the end result is that the query has become complex because of the lots of JOINS.

Can I consider this as an antipattern? My thoughts is that, there should be some limit on the number of joins..

Thanks.
 
Gian Franco
blacksmith
Ranch Hand
Posts: 979
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think a lot of joins are an antipattern. It is more
a result of the design of the database in combination
with the particular query someone asked for. So it might
be perfectly legitimate and necessary.

You could 'hide' the joins by creating one or more views
that will take care of the joins and make your original
query select from these views instead.

Cheers



 
Bill Karwin
author
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Mark,

I wouldn't set a hard limit on the number of joins, or consider too many joins to be an antipattern. This would be like saying it's a Java antipattern that you have more than a certain number of classes. Sometimes you do have too many classes in one application, but the same number of classes in another application might be just what is needed.

In my experience, it's more common for developers to use fewer joins than they need, than for them to use too many joins. They read some blog that says "joins are slow" and so they try anything they can to avoid using joins -- even if by eliminating joins they have actually made the problem harder to solve or slower to run. See the example given by Razvan Popovici earlier this week: http://www.coderanch.com/t/508765/JDBC/java/SQL-Antipatterns

One common place I see people use too many joins is when you use the Entity-Attribute-Value design and you try to query a result set from it on one row per entity, as if it were a proper table. You end up doing an extra join to your EAV table for each attribute. The EAV design is of course an antipattern.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic