Friday, March 30, 2012

Relationships vs. Joins

Hi,

I was wondering when designing a database with multiple tables with PKs and FKs in place, why would I think to map those FKs and PKs between tables hence making relationships while I can retrieve whatever I need with joins without creating any relationships?

Hi loopool.

There's lots of reasons you'd want to consider this, here's a couple:

1) Data integrity - constraints (pk's, fk's, checks, defaults, unique, etc.) are primarily used to enforce data integrity within your database (i.e. domain, entity, referential). They enforce things like ensuring valid values exist for a given parent/child type record, ranges of valid data, duplicates, etc. FK's can also ensure deletes don't allow 'orphaned' records in your system through cascading actions. See the following topic in books online for a brief discussion of data integrity:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/475233a9-b46f-4aa8-aa13-d388beb9f069.htm

2) Read optimization - the query optimizer can make use of many of these types of constraints for use with optimizing query plans in a variety of different ways - one of which can be when you are using joins for example as you mentioned above.

There's a variety of other reasons for using them, but that's a start...HTH,

No comments:

Post a Comment