I have a names and addresses table:



 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


 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.

  • It depends on how it will be used. If it's a data warehouse, it may be better denormalized with columnstore indexes. There's no way someone can tell you what the best solution is without knowing all of the requirements such as what type of queries will be run against it and what goals and limitations are involved. Nov 20, 2019 at 15:53
  • Thanks, @TonyHinkle, I'll revise my question a bit so that I'm not asking for the best solution.
    – Missy
    Nov 20, 2019 at 16:08

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.