Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> 1:1 relationships
 

Pages: 1
LLBLGen Pro Runtime Framework
1:1 relationships
Page:1/1 

  Print all messages in this thread  
Poster Message
obzekt
User



Location:

Joined on:
29-Apr-2004 18:18:59
Posted:
49 posts
# Posted on: 29-Apr-2004 18:29:32.  
Hello. From what I understood LLBLGen supports both types of 1:1 setups. What is the recommended approach in theory and in practice with LLBLGen?

For example, if I have Order and OrderDetails, what is best:

1. Use OrderID as PK to both tables (identity in Order and FK in OrderDetails).

2. Use OrderDetailsID identity PK (for consistency with the rest of the schema that uses identity keys everywhere) and add a unique constraint on OrderID FK.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37236 posts
# Posted on: 29-Apr-2004 18:50:07.  
obzekt wrote:
Hello. From what I understood LLBLGen supports both types of 1:1 setups. What is the recommended approach in theory and in practice with LLBLGen?

1:1 relations can be the following:
A and B have a 1:1 relation if:
- PK of A has a relation with PK of B and B is then an FK. Example: Person and Customer
- PK of A has a relation with field(s) in B which form an FK pointing to PK of A and are not part of the PK and have a unique constraint applied to them. Example: AddressID in Customer, pointing to AddressID in Address and Customer.AddressID has a unique constraint.

so if you want 1:1 relationships, you should setup your relational model like I described above.

Quote:

For example, if I have Order and OrderDetails, what is best:
1. Use OrderID as PK to both tables (identity in Order and FK in OrderDetails).
2. Use OrderDetailsID identity PK (for consistency with the rest of the schema that uses identity keys everywhere) and add a unique constraint on OrderID FK.

Order and OrderDetail do not have a 1:1 relation but a 1:n relation, IF each order has just 1 orderdetail record. If more than 1 orderdetail record can be in 1 order, it's a 1:n relation from Order -> OrderDetail and a m:1 relation from OrderDetail -> Order. Regular Smiley

An OrderDetail record has to be uniquely identifyable, and if an order has more than 1 orderdetail (what's most of the time is the case) 1) will not be sufficient.
2) is the best solution, however forget the UC on OrderID if you want to have more order detail records in an order. I'm not sure if that's the case in your situation.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
obzekt
User



Location:

Joined on:
29-Apr-2004 18:18:59
Posted:
49 posts
# Posted on: 29-Apr-2004 19:17:37.  
Otis wrote:
1:1 relations can be the following:
A and B have a 1:1 relation if:
- PK of A has a relation with PK of B and B is then an FK. Example: Person and Customer
- PK of A has a relation with field(s) in B which form an FK pointing to PK of A and are not part of the PK and have a unique constraint applied to them. Example: AddressID in Customer, pointing to AddressID in Address and Customer.AddressID has a unique constraint.

so if you want 1:1 relationships, you should setup your relational model like I described above.


That's what I'm asking, what is, if any, the theoretical and/or practical benefit of using one method or the other? Maybe your first suggestion is preferable because the 1:1 rel. is more explicitly defined. Is there any difference in speed for the code that LLBLGen produces?

Thank you.

p.s. OrderDetails was a bad example on my part..
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37236 posts
# Posted on: 29-Apr-2004 20:31:24.  
obzekt wrote:
Otis wrote:
1:1 relations can be the following:
A and B have a 1:1 relation if:
- PK of A has a relation with PK of B and B is then an FK. Example: Person and Customer
- PK of A has a relation with field(s) in B which form an FK pointing to PK of A and are not part of the PK and have a unique constraint applied to them. Example: AddressID in Customer, pointing to AddressID in Address and Customer.AddressID has a unique constraint.

so if you want 1:1 relationships, you should setup your relational model like I described above.

That's what I'm asking, what is, if any, the theoretical and/or practical benefit of using one method or the other? Maybe your first suggestion is preferable because the 1:1 rel. is more explicitly defined. Is there any difference in speed for the code that LLBLGen produces?

There is no difference, as both will result in a filter which fetches 1 row Regular Smiley

I'd suggest to go for the new key in the orderdetail row if that is containing data which can be seen as a separate entity. If you just have a 'specialization' table, like employee and manager, where manager has an FK (which is also the PK) pointing to employee and only contains extra fields for employee, I'd go for the same key.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
ctadlock
User



Location:
Seattle, WA
Joined on:
12-Feb-2004 05:22:11
Posted:
60 posts
# Posted on: 21-Nov-2004 23:35:55.  
For the situation..

Quote:

AddressID in Customer, pointing to AddressID in Address and Customer.AddressID has a unique constraint.


If it is possible that not all Customer's have Addresses (ie. Customer.AddressID can be null), how can you apply a unique constraint to Customer.AddressID and have it work properly? SQL Server won't let 2 rows of a unique constraint have null values.


Thanks
CT
Craig Tadlock
Systems Architect
Ramp Technology Group
http://blog.rampgroup.com
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.