Address table

  • 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?

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • That is exactly what I wanted to ask. Thanks.

  • 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

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

  • 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.

  • 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

  • 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

  • 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).

  • 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.

  • 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?

  • 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.

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

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