Design question - adding soft links to tables
-
Hi, I have a design question that I was hoping a 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 Ryan
-
Hi, I have a design question that I was hoping a 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 Ryan
I would go on the side of storing information regarding addresses in the address table rather than the account table. So have a column on the address table showing it to be the primary address - something like
addressContext nvarchar(max). In the end it's not a deal-breaker but it does mean that you are de-coupling your data(not sure that is the correct term) by only storing account information in the account table and address information in the address table. Also don't use varchar(100) as you will run out of room sooner than you think - use nvarchar(max) instead, there will not be much of a performance hit and you are allowing for large addresses.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Hi, I have a design question that I was hoping a 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 Ryan
I would never do the PrimaryAddKey flag on the Account table, I don't even care about the performance difference (unless you have mega addresses it would be minuscule) it introduces a vulnerability into your schema and is therefore wrong. I would however create an Account view that included the primary address but then I'm a great beleiver in views :)
Never underestimate the power of human stupidity RAH
-
I would go on the side of storing information regarding addresses in the address table rather than the account table. So have a column on the address table showing it to be the primary address - something like
addressContext nvarchar(max). In the end it's not a deal-breaker but it does mean that you are de-coupling your data(not sure that is the correct term) by only storing account information in the account table and address information in the address table. Also don't use varchar(100) as you will run out of room sooner than you think - use nvarchar(max) instead, there will not be much of a performance hit and you are allowing for large addresses.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
I'd disagree with the nvarchar(max), I would only ever use nvarchar if it was a requirement to store unicode and I would place a size limit on the address, make it large but don't use max unless you are intending to use it which an address will never do. If some pillock has a couple of thousand characters in his address then truncate the idiot, not the data, the pillock!
Never underestimate the power of human stupidity RAH
-
Hi, I have a design question that I was hoping a 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 Ryan
As others have said, I'd leave the primary indicator in the address table, not the account table. Conceivably, the primary address for an account could change, in which case the address table is updated. The account hasn't changed - the primary address has. Again, as others have noted, create an acount view that includes the primary address. To that extent, create an account view that shows all addresses and denotes which one is primary. Tim
-
As others have said, I'd leave the primary indicator in the address table, not the account table. Conceivably, the primary address for an account could change, in which case the address table is updated. The account hasn't changed - the primary address has. Again, as others have noted, create an acount view that includes the primary address. To that extent, create an account view that shows all addresses and denotes which one is primary. Tim
-
I would never do the PrimaryAddKey flag on the Account table, I don't even care about the performance difference (unless you have mega addresses it would be minuscule) it introduces a vulnerability into your schema and is therefore wrong. I would however create an Account view that included the primary address but then I'm a great beleiver in views :)
Never underestimate the power of human stupidity RAH