I'm still at the design stage for a new system, and I've noticed there are several places where different types of object share similar fields.

In this case, we have customers, offices, warehouses etc. that have address fields like Address1, Address2, ... Postcode. So, I was strongly considering making a new Addresses table with all these fields in, and an ID, and linking to that with foreign keys for all of the different types that contain addresses. The same thing could be done for groups of telephone/fax numbers too.

To illustrate this point, here's the current situation in an abstract form...

Customers        Offices
- CustomerID     - OfficeID
- FirstName      - OfficeName
- Surname        - Telephone
- Address1       - Address1
- Address2       - Address2
- Address3       - Address3
- Address4       - Address4
- Postcode       - Postcode

...and after the change...

Customers        Offices         Addresses
- CustomerID     - OfficeID      - AddressID
- FirstName      - OfficeName    - Address1
- Surname        - Telephone     - Address3
- AddressID      - AddressID     - Address3
                                 - Address4
                                 - Postcode

The objective here isn't really normalization (I'm not intending to eliminate duplicate addresses here, even though it may be possible) but to help keep similar subtypes of data consistent. It also means that in the model code, I can treat all addresses in the same with with one class, whatever they belong to. I hope that the overhead in joining these tables wouldn't be that high, especially since I don't always need the addresses for all use cases.

Your opinions/experiences please!

  • 1
    I think it makes total sense to map those entities (in this case, addresses) out to their own table. This will also make future maintenance easier, like if you need to add another column to the address data you update one table instead of several. – JNK Jul 18 '14 at 14:56
  • @JNK Yes, absolutely... I can think of many benefits myself and no caveats. I just wanted to see if anyone could think of a reason not to, or anyone who can say that they've had a good experience using this kind of abstraction. – BoffinBrain Jul 18 '14 at 16:31

Yes, move it. The extra join overhead will be minimal (if the right indexes are in place!) and you will get a performance improvement for all the other queries which don't use addresses because you will get more rows per page in Customers and Offices.

Down the road, any master data management (MDM) or data clean-up will be easier with the values all in one place.

Sticky points to look out for are common definitions of column semantics (is Address4 the city, say, for absolutely every possible address?). This could be important for reporting, for example, or summarising sales by region. Uniqueness could be difficult to define for any natural keys you may choose to create on Addresses.

  • Thanks for the opinion. The simplification of the address fields was purely for the example and we run our address data through a service which normalizes it, so no worries there! I guess I just wanted some extra assurance. I'll probably accept your answer in a few days. – BoffinBrain Jul 21 '14 at 15:28

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.