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..
Sean Clark ---> I love this place!!!
Me ------> I definitely love this place!!!
Joined: Dec 16, 2003
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.
"Eppur si muove!"
Joined: Aug 02, 2010
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.