Database Relations

Question

establishes the right relationship in a database with anything else but data integrity?

improve or hinder performance?

Answer 1

I have to say that the right relationship will help people understand the data (or the intent of the data) better than not to miss them, especially since the overall cost is pretty low in maintaining them.

Their presence does not interfere with performance, except from an architectural point of view (as others have pointed out, data integrity can sometimes cause foreign key violations, which can have some kind of effect), but IMHO is outweighed by many advantages (if used correctly).

I know that you did not ask whether to use FK or not, but I thought that I would just add a couple of points of view on why to use them (and I have to deal with the consequences):

There are other considerations, for example, if you ever plan to use ORM (maybe later), you will need foreign keys. They can also be very useful for importing and exporting ETL / data, and then for reporting and storing data.

It is also useful if other applications will use the scheme - because foreign keys implement the basic business logic. Thus, your application (and any others) should only know the relationships (and read them). It will maintain data consistency and, most likely, reduce the number of data errors in any consuming applications.

Finally, this gives you a pretty decent hint as to where to put the indexes, since most likely you will be looking for table data by FK value.

Answer 2

As long as you have obvious in-place indexes matching foreign keys, there should be no noticeable negative effect on performance. This is one of the most reliable database features you have to work with.

Answer 3

This neither helps nor worsens performance. The only obstacle is integrity checking on insert / update / delete.

Foreign keys are an important part of database design because they provide consistency. You should use them because it offers the lowest level of protection against data crashes that could destroy your applications. Another advantage is that the database tools (visualization / analysis / code generation) use foreign keys to communicate data.

Answer 4

Are database connections improving or hindering performance?

Like any tool in the toolbar, the results you get depend on how you use it. A well-defined relationship and a well-designed logical database can be a huge performance benefit - consider, for example, the difference between searching through normalized and denormalized data.

Answer 5

Depending on the database engine, relationships defined using foreign key constraints can benefit performance. The restriction allows the engine to make certain assumptions about the existence of data in tables on the parent side of the key.

A brief description of MS SQL Server can be found at http://www.microsoft.com/technet/abouttn/flash/tips/tips_122104.mspx . I do not know other engines, but the concept will make sense on other platforms.

Answer 6

Relationships in the data exist whether you declare them or not. Declaring and forcing binding of links through FK restrictions will prevent some types of data errors at a low cost of data verification during insertions / updates / deletes.

Declaring cascading deletes through relationships helps prevent some kinds of errors when deleting data.

Knowledge of relationships helps to use data flexibly and correctly when generating queries.

Designing tables can make relationships more obvious and more useful. Using relationships in data is the primary reason for using relational databases in the first place.

Answer 7

On the impact on performance: in my experience with MS Access 2003, if you have a multi-user application and you use relationships to provide a significant amount of referential integrity, you can get great success in terms of response time for the end user, user.

There are various ways to ensure referential integrity. I decided to adopt some rules in the relationship, build more enforcement in the interface and live with some loss of RI. Of course, in a multi-user environment, you want to be very careful with this freedom.

Answer 8

In my experience with creating performance-sensitive databases, foreign keys significantly degrade performance because they need to be checked every time an inserted / updated record is inserted or the main record is deleted. If you need proof, just look at the execution plan.

I still store them for documentation and for tools, but I usually turned them off, especially in high-performance systems, where access to the database is only through the application layer.