0

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.

  • 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. – Tony Hinkle Nov 20 '19 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 '19 at 16:08
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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