Address dimension or not?

View previous topic View next topic Go down

Address dimension or not?

Post  tribe84 on Mon Dec 14, 2015 3:06 pm

Hey All,

My team is debating internally whether or not we should be creating a separate dimension of address information. The use case is a warehouse for a mail marketing agency, so address is quite important for a multitude of reasons.

We have a couple of pieces of address information flowing in (like Bank address, Customer Address (Our Client's customers), Mailing List Address (or Manifests), And Client Address. We might also get information in bits and pieces from other information that we might need to tie to a specific customer based on address comparisons.

We also do geocoding on our addressess to augment, standardize and validate our addresses that come in.

In total, we are storing the following fields for any given address:
DeliveryLine1
DeliveryLine2
LastLine
DeliveryPointBarcode
StreetNumber
ApartmentNumber
ApartmentUnitType
StreetName
StreetSuffix
Locality
Region
ZipCode
ZipCodePlusFour
DeliveryPoint
DeliveryPointCheckpointDigit
Latitude
Longitude
RecordType
ZipType
CountyFIPS
CarrierRoute
ResidentialDeliveryIndicator
Precision
DPV
Vacant
Active
EWS

thats 27 fields in total.

My colleague is of the opinion that address should go into each dimension (Customer, Bank, Client, Manifest). While I agree that in simple cases where we store Address1, Address2, City, State, Zip it would make sense, but we store a significant amount of added information about an address, with more bits and pieces being added later on (potentially). I make the contention that something like this would be better suited as a separate dimension. Any thoughts?

tribe84

Posts : 1
Join date : 2015-12-14

View user profile

Back to top Go down

Re: Address dimension or not?

Post  ngalemmo on Mon Dec 14, 2015 5:47 pm

Having a separate address dimension for mail marketing makes sense.  Standardization, geo-coding and various marketing related attributes are all good reasons to do it that way.

One technique for reducing overhead of natural key lookups would be to create a 32 bit CRC of the natural key attributes and use it an a non-unique alternate key index to the table.  This saves you from having to build an index on the entire natural key.  This is a much smaller index and generally performs better than a unique index on the entire natural key. Also, do not include zip code extensions (i.e. the Plus 4 part) in the natural key as it may change. The 5 digit zip is good enough and much more stable.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum