Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Let's imagine a data model with customers and invoices. There is a 1 to n relationship between a customer and its invoices. We uses an ORM (like Hibernate).

One can explicitely implement the 1-n relationship (using JPA for example) or not. If not, then one must do a bit more work to fetch invoices.

However, it is much easier to maintain, improve and develop the data model of applications where relationships between objects are not explicitely implemented in the database.

My question is, has anyone noticed a significant performance impact when not implementing the relationships in the database?

share|improve this question

closed as not a real question by gnat, Walter, MainMa, Yusubov, Robert Harvey Nov 2 '12 at 0:03

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
There is no such thing as "relationship in the database". They are all atomic values with either constrainst or local indexing. –  Euphoric Oct 31 '12 at 13:32
2  
@Euphoric - I think it's reasonable to call the effect of foreign-key constraints (which I assume is what the OP is referring to) "relationship[s] in the database". –  Mike Partridge Oct 31 '12 at 14:02
    
Indeed this is what I refer to. –  JVerstry Oct 31 '12 at 14:45
add comment

1 Answer

up vote 1 down vote accepted

When I implement a relationship like that, there are 2 parts to it:

  1. The "foreign key"
  2. The "index"

The foreign key is a constraint which says that the CustomerId in the Invoice table must exist in the Customer table. This (probably) doesn't give you any performance improvement (in fact there's a performance hit to do the check), but it's there to catch errors and make absolutely sure that the data is consistent. Weird things can happen. I don't suggest skipping this.

The index is when you index the Invoice table by the CustomerId column. I know that in SQL Server you have to specify this separately from the foreign key. The index will give you a performance improvement, if you ever look up invoices for a given customer. However, maintaining the index takes time and space, so there's a tradeoff.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.