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,

Relationship map using CTEs

For instance, if I have the following values in a table
Id ParentId
1 null
2 1
3 1
4 2
5 4
I understand how to traverse this relationship tree using CTE but how would
I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
Return results
Id RelatedId
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 4
2 5
3 1
3 3
4 1
4 2
4 4
4 5
5 1
5 2
5 4
5 5
Basically, an Id is related to every id that descends from it (directly or
indirectly) and an id is related to every parent link upwards from it.
Is this possible with CTEs?"Arif" <Arif@.discussions.microsoft.com> wrote in message
news:2B66D0F9-7E43-41C1-9B93-6DF147E5EB83@.microsoft.com...
> For instance, if I have the following values in a table
> Id ParentId
> 1 null
> 2 1
> 3 1
> 4 2
> 5 4
>
> I understand how to traverse this relationship tree using CTE but how
> would
> I build a map where I say Id 1 is related to Id 2, 3, 4, 5... Like so,
>
> Return results
> Id RelatedId
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 4 1
> 4 2
> 4 4
> 4 5
> 5 1
> 5 2
> 5 4
> 5 5
>
> Basically, an Id is related to every id that descends from it (directly or
> indirectly) and an id is related to every parent link upwards from it.
> Is this possible with CTEs?
Yes. Here's an example of how to enumerate a transative relation using a
CTE:
Create Table T
(
ID int primary key,
ParentID int references T
)
insert into t(id,parentid)
select 1,null
union all select 2,1
union all select 3,1
union all select 4,2
union all select 5,4
go
with Related(ID, RelatedID, Distance)
as
(
--anchor with reflexive member
select ID, ID, 0 from T
--union in transitively related members
union all
select r.ID, t.ID, r.Distance + 1
from T t join Related r
on t.ParentID = r.RelatedID
)
select * from Related
order by id, RelatedID, Distance
/* results
ID RelatedID Distance
-- -- --
1 1 0
1 2 1
1 3 1
1 4 2
1 5 3
2 2 0
2 4 1
2 5 2
3 3 0
4 4 0
4 5 1
5 5 0
*/
David