1:1 relationships

Posts   
 
    
obzekt
User
Posts: 60
Joined: 29-Apr-2004
# 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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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.

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. simple_smile

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 | Lead developer LLBLGen Pro
obzekt
User
Posts: 60
Joined: 29-Apr-2004
# 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..

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 simple_smile

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 | Lead developer LLBLGen Pro
ctadlock avatar
ctadlock
User
Posts: 60
Joined: 12-Feb-2004
# Posted on: 21-Nov-2004 23:35:55   

For the situation..

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