primary flag or foreign key?

Last Post 12 Jan 2008 06:09 AM by SQLUSA. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
RayMetz100
New Member
New Member

--
08 Jan 2008 12:47 PM
Which is better?

CustomerAccount
CustomerAccountId
PrimaryCustomerAddressId (FK)

CustomerAddress
CustomerAddressId

or

CustomerAccount
CustomerAccountId

CustomerAddress
CustomerAddressId
IsPrimaryFlag

If someone has a link to a longer document on the web or sqlmag article, that would be great.

Thanks,

Ray Metz
SQLUSA
New Member
New Member

--
08 Jan 2008 01:05 PM
The second one is the low maintainance option. You would need some justification for the first one.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
08 Jan 2008 11:12 PM
We need some clarification.

What is the exact rule? Is this a business rule? Or referential constraint?

IsPrimaryFlag appears to be an attribute of the Address entity.

We use our human intelligence to put it into immediately the data integrity context.

Russel...what if the non-primary address entered first into the system? How the first design any different than the second?

Without knowing more about it, trigger or application enforcement would both be appropriate.



Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!

SQLUSA
New Member
New Member

--
09 Jan 2008 07:25 AM
>To that end we must enforce data integrity at the data tier, rather than rely on the application to do so for us.

Agreed. Frequently at the enterprise level, the lonely trigger becomes the ultimate enforcer of business rules.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/ssis/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
12 Jan 2008 06:09 AM
If secondary addresses are rare, it would make sense to store them in a seperate table. That would certainly help with performance.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
tom27
New Member
New Member

--
14 Jan 2008 11:22 PM
What I feel is we should treat customerAccount and customerAddress as 2 entities and there should be a junction table to associate them if the relation type is many to many.

SQLUSA
New Member
New Member

--
15 Jan 2008 12:48 AM
It makes sense, but you still have the primary address issue to resolve.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
19 Jan 2008 12:35 AM
Going with the

Company
CompanyAddressXref
Address

model, PrimaryAddressID (FK) in the Company table is the most logical implementation of this function.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ The Best SQL Server 2005 Training in the World!
tom27
New Member
New Member

--
28 Jan 2008 02:27 AM
Basically it's simple. The PrimaryAddress should be the attribute of the relation between Customer and address. So it should be in the junction table.
tom27
New Member
New Member

--
28 Jan 2008 02:29 AM
What I ment was there can be 3 types of relationships possible between Customer and Address entities.

1) A customer can have only one address One To One
2) A customer can have more than one address One to Many
3) An address can be shared by more than one customer Many to One

If we have both 2 and 3 then it is called a Many to Many relation ship and we require a junction table.

The indicator is primary address is truly the attribute of the relation and it should be there in the junction table.
SQLUSA
New Member
New Member

--
28 Jan 2008 10:54 AM
Agreed.

I assume the table is not too large?

How large?

Is performance an issue?

Kalman Toth, Business Intelligence Architect
SQLUSA: http://www.sqlusa.com - OLAP Reporting, Performance Management
tom27
New Member
New Member

--
28 Jan 2008 07:31 PM
I don't think large tables can cause a performance issues if it's properly indexed.
SQLUSA
New Member
New Member

--
28 Jan 2008 11:31 PM
Agreed that indexing is absolutely essential.

How large is large for you?


5 millions? 50 millions? 500 millions?

Some consumer databases hit 100 millions easy.

It may not be the case for the current advice seeker.....

Kalman Toth, Business Intelligence Architect
SQLUSA: http://www.sqlusa.com - OLAP Reporting, Performance Management

You are not authorized to post a reply.

Acceptable Use Policy