MS SQL Server - Database Design Assistance

Author

Database Design Assistance

jasonl22@yahoo.com

2006-04-08, 3:23 am

I have 2 tables that will be created from flat files and recreated on a
daily basis with 300,000+ records and growing daily (tables will be
truncated and re-loaded via a batch process because we do not "own" the
data). I would like to extract the address information from these
tables and create a simple database design that will be relational and
efficient (there are additional Customer and Account fields but these
are the fields currently of interest).

Customer [table name]
TaxID [PK]
Addr1
Addr2
City
State
Zip
Country
MAddr1
MAddr2
MCity
MState
MZip
MCountry

Account [table name]
AccountNumber [PK]
TaxID
SecTaxID
Addr1
Addr2
City
State
Zip
Country

Every Customer in Customer table will have 2 addresses per TaxID,
Mailing and Physical even if they are the same. Every AccountNumber
will have exactly 1 address (Mailing) associated with it. So, if you
are a customer with 2 accounts, you would have 1 record in the customer
table (with both address and Mailing address populated) and 2 records
in the Account table. All 4 addresses could be the same or unique.
Mailings will EITHER go out at the Account level OR the Customer level
so I'm not sure if it is beneficial to combine the addresses into one
table or just create some sort of Account address table and another
customer address table but then i get one-to-one relationships and I'm
not sure how beneficial that is. This is how the data will be received
from the vendor and there isn't any leeway there. Any assistance that
could be provided would be greatly appreciated.

John Bell

2006-04-08, 1:23 pm

Hi

If you do not store all the addresses as received you will probably end up
with a data cleansing excercise to make sure that you are not duplicating the
same address that is input slightly differently. Moving the address out to
another table will be a good idea if you have occassions to retrieve the
customer/account information without the address details. I would start with
a normalised design and if performance is being severely compromised you can
then de-normalise.

John

"jasonl22@yahoo.com" wrote:

> I have 2 tables that will be created from flat files and recreated on a
> daily basis with 300,000+ records and growing daily (tables will be
> truncated and re-loaded via a batch process because we do not "own" the
> data). I would like to extract the address information from these
> tables and create a simple database design that will be relational and
> efficient (there are additional Customer and Account fields but these
> are the fields currently of interest).
>
> Customer [table name]
> TaxID [PK]
> Addr1
> Addr2
> City
> State
> Zip
> Country
> MAddr1
> MAddr2
> MCity
> MState
> MZip
> MCountry
>
> Account [table name]
> AccountNumber [PK]
> TaxID
> SecTaxID
> Addr1
> Addr2
> City
> State
> Zip
> Country
>
> Every Customer in Customer table will have 2 addresses per TaxID,
> Mailing and Physical even if they are the same. Every AccountNumber
> will have exactly 1 address (Mailing) associated with it. So, if you
> are a customer with 2 accounts, you would have 1 record in the customer
> table (with both address and Mailing address populated) and 2 records
> in the Account table. All 4 addresses could be the same or unique.
> Mailings will EITHER go out at the Account level OR the Customer level
> so I'm not sure if it is beneficial to combine the addresses into one
> table or just create some sort of Account address table and another
> customer address table but then i get one-to-one relationships and I'm
> not sure how beneficial that is. This is how the data will be received
> from the vendor and there isn't any leeway there. Any assistance that
> could be provided would be greatly appreciated.
>
>

Sponsored Links