Design question - adding soft links to tables 2

[RyanEK]

Hi,

I have a database design question that I was hoping a DB professional could answer. Lets say I have the following one-to-many table relationship between an Account and its Addresses.

create table account (
   acctkey int,
   acctname varchar(100),
   active bit
)

create table acctaddress (
   acctkey int,
   addkey int,
   street1 varchar(100),
   isprimary bit  
)

An account can have many addresses but only one can be the primary. Does it make sense to include a soft link in the main table to hold the key of the address that is the primary? ie.

create table account (
   acctkey int,
   acctname varchar(100),
   active bit,
   primaryaddkey int <---
)

This is purely to return the primary address of account (with the best performance in mind).
Is this a bad design? Does the need to sync the account and acctaddress tables outweigh the performance advantages?
I would appreciate any thoughts on this.

Thanks
Ry

 

[JohnDuncanTB]

No need for the additional primaryaddkey column. That would mean an update to the acctaddress table isprimary column requires an additional update to the account table. You can accomplish the same thing with a view.

CREATE VIEW dbo.acctprimaryaddress 
AS
SELECT
  a.acctkey, a.acctname, a.active,
  aa.addkey, aa.street1
FROM dbo.account AS a
  LEFT OUTER JOIN dbo.acctaddress AS aa ON a.acctkey = aa.acctkey AND aa.isprimary = 1

The OUTER JOIN ensures that every account will be listed even if they have no address marked as primary.

--JD
"Behold! As a wild ass in the desert go forth I to my work."

 

[Olaf Doschke]

Well, the primaryaddkey would not be additional to the cbit, would it? It would replace it.

In the design with the primaryaddkey field, you have a rule about N records, of which only one should be True, the others should be False. This is quite unpractical to put into a constraint.

On the other side you can easily make sure only one such a bit value is true, by always first setting all isPrimary of the same accoutn to False and then only one to True.

The foreign key primaryaddkey int has another disadvantage, in that you can have a reference to an acctaddress, which doesn't belong to the account. That would be much harder to resolve or put into a constraint.

One easy way to handle this is to allow more than one primary address, anyway. The rule to only have one main address is rather in our head than a real constraint, is it. But that's the easy way out of this dilemma.

There is a simpler way out, that is easy to define and check: Introduce an order field in acctaddress, which starts at 1 for the first address and increments for further addresses. You can define the main address will always be the one with order=1 and you can put a constraint on the acctaddress table on the foreign key acctkey and the order. The combination of both must be unique. This has the benefit of enabling your users to defnie the order in which addresses are displayed, too. Letting a user change the primary address then is indirectly reordering the addresses. If you want to allow specifying more than a single primary address you can combine order and isprimary fields and have no conflict or redundance, the order then merely is storing the display order.

Bye, Olaf.

 

[RyanEK]

Great responses guys, thank you. I will take on board the use of a view, and look into applying an ordering on the addresses.