Sql-server – How to Design Name and Address with Multiple Addresses

database-designsql server

I have a names and addresses table:

 Name
 Address
 AddressType
 ID
 CustID

Sample:

 Bob Smith, 123 Main St, Corporate, 1, 1
 Bob Smith, 123 Main St, Home, 2, 1
 null, P.O. Box 123, Warehouse, 3, 1

and I have a customer table

 ID
 TaxID
 BusinessType
 etc

 1, 94-123456798, Consultant

The customer may have many addresses.

I need to query on specific address types and do CRUD operations. For example, what is the corporate address? Retrieve and edit the DBA and Corporate addresses. Where is the inventory kept?

So my first inclination is to modify the customer table to add CorpAddressID, InventoryLocationID, etc. There might be a slew of old and new addresses but certain addresses have to be designated clearly.

So then I thought I could just reference the address type — Customer join Address where AddressType = Corporate — I'm worried about doing this and I have no idea why. Like having to hard code where AddressType = Corporate and relying on the Corporate designation being stored in the database.

I checked this out and it was similar but not close enough to help:

Data Warehouse Kimball Customer and Customer Address Dimensions

So, I ask, is there a compelling reason to not reference the name and address field IDs directly from fields such as CorpAddressID, DBAAddressID, etc?

Any thoughts or feedback would be greatly appreciated.

Best Answer

If there is something in the software or data entry that mandates choosing the Address Type before successful submission, then I don't think you'd need the Address Type ID because you could just index and query the Address Type column.

If the Address type being blank is a concern, then I'm not sure that an Address Type ID would help because you'd still be dealing with nulls or empty fields.

Related Question