Address table

Question

Address table

yaip

SSCrazy

Points: 2656
More actions
November 21, 2004 at 11:30 am

#84263

I am fairly new to SQL Server though I have been exposed to other databases. I am designing an application that will have several addresses, e.g., Bill To, Ship To, Mailing, etc. I am thinking of having a table for addresses. The downside of this is that now I will have two reads instead of one on the database. This might impact the performance.
Is this advisable in SQL Server? Is this a standard practice? What are the recommendations?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

If your question is: "Is it better to have a single table with the capacity for multiple addresses in a single row, or one master table linked to an address table?", basic database theory would tell you to create a separate address table and this is what I would recommend. This is how relational databases are intended to work.

The only cirumstances under which I would even consider storing the data in a single table are where the number of different address types is known and unlikely to change, and where each address will always be populated.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yaip SSCrazy Points: 2656 More actions · Answer 2

yaip

SSCrazy

Points: 2656

November 21, 2004 at 4:41 pm

#530979

That is exactly what I wanted to ask. Thanks.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 3

Here is a short normalized version of what you need to do:

tblParty

PartyID (PK)

PartyName

tblAddress

AddressID (pk)

AddressLine1

AddressLine

PostalCode

CityName

StateCode

tlkpAddressType

AddressTypeCode (pk)

AddressTypeDesc

tblPartyAddressType

PartyAddressTypeCode (pk)

PartyID

AddressID

AddressTypeCode

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

Don't forget to include columns for beginning and ending dates, so when your customer moves you won't lose your historical info. MTCW

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 5

That is why I said short. 🙂 Based on the business logic for what he needs to do, you may or may not need audit, status, and effectivity columns included.

dg78 SSC Veteran Points: 296 More actions · Answer 6

Hi,

I am also new in sql and i have the same issue but with addresses for several entities.

Could you detailed if i have the following tables :

tblCustomer

CustomerID (PK)

CustomerName

(other fields)

tblSupplier

SupplierID (PK)

SupplierName

(other fields)

tblEmployee

EmployeeID (PK)

EmployeeName

(other fields)

tblAddress

AddressID (pk)

AddressLine1

AddressLine2

PostalCode

CityName

StateCode

BeginDate

EndDate

how could be the joined tables ?

Thanks

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 7

With that specific design, you will need a separate resolver table for each type of party (employee, supplier, customer)

tbl_EmployeeAddress

EmployeeAddressID (pk)

EmployeeID

AddressID

AddressType

would be an example of the Employee Address table

yaip SSCrazy Points: 2656 More actions · Answer 8

When designing State Code table, I am always a bit torn between two designs.

Here they are:

Design 1:

StateID int (pk)

StateCode varchar

StateName varchar

Design 2:

StateCode varchar (pk)

StateName varchar

The advantage of Design 1 is that I can change the length of StateCode if I have international states where this table can act as a province table.

Where as, the advantage of Design 2 is that I can have StateCode as FK in other tables instead of having a surrogate key (thus saving a read).

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 9

The correct way is close to the first way.

tlkpStateCode

StateID int (Identity) (pk)

StateCode varchar(10)

CountryCode varchar(2) (Using ISO 2 character standard) (FK tlkpCountry)

StateFullName varchar(50)

Unique index on StateCode + CountryCode

This design will allow you to use the same state code for different countries for example:

ME US Maine

ME RU Mari-El

would be valid but with design 2, you could not do it with out using a compound key (State + Country) and you would have to carry both colums as a foreign key, not the one like the design above.

yaip SSCrazy Points: 2656 More actions · Answer 10

The other "standard" that I like to keep (as far as possible) is to have non-intelligent, surrogate value for keys.

For example, I would design the Address Type table as follows:

AddressTypeID integer (pk, identity)

AddressTypeDesc nvarchar(50)

...Audit stamp...

Any better suggestions?

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 11

Generally, I do also. But, there is an argument that is valid for keeping it simple with code tables. The main argument for doing it with dumb primary keys falls to cascading/restricting updates and deletes if you have an intelligent key that the user can change.