February 11, 2009 at 7:59 am
Hi, simple question I hope.
In a database wher I have a few different entities, eg Organisations and Individuals, both of which have an address. Would ther be good reasond to not go with a combined address table. i.e. the address table would have an owner ID and an Owner Entity field.
Or would you go with two seperate tables... OrganisationAddress and IndividualAddress.
I guess there are pro's and con's to each approach.
Thanks
Allen
February 11, 2009 at 8:21 am
I would go with a single Address table and then join tables to the different entities that use it. This allows you to have a single source for address validation and updates, making maintenance easier. Yes, you'll have to join two tables to get data instead of one, and you should put individual items like suite number into the join table, seperate from address, meaning you'll always have to join across those tables, but it really will make the long term viability of the data much better.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 11, 2009 at 12:08 pm
Ah, if only things like this, that SEEM simple, actually WERE simple.
Having had to build databases to track this kind of data, I'll just throw out a few things to consider when you build your design:
1) Are individuals also part of organizations. Always, sometimes, or never?
2) If the answer to 1) is 'possibly', you probably want a means to associate individuals addresses with their organizations, primarily, but you'll also want a way to give them individual addresses/contact info.
3) When you plan how you deal with 1 and 2, you'll want to consider the possibility that one individual might be part of more than one organization.
4) You should consider whether there's any possibility that you'll need 'historical' data, i.e. could there be a case where you need someones or somethings previous address, or, their previous organization(s)? I.E. account for the possibility that people can switch organizations, and that individuals and organizations can both move, and figure out whether that could possibly impact this data's relationships with other data.
5) Account for hierarchy within organizations. Many times, a single organization may have multiple addresses based on what the particular business with them is (i.e. accounts receivable vs. management).
6) Don't forget the possibility that organizations might merge, or even de-merge. I.E. account for the possibility that organizations in general can undergo various changes that could possibly impact this data's relationships with other data.
7) Also very important is how the other 'pieces' of your system are going to be interacting with this data. Consider the fact that in *MANY* cases, other tables are going to need to be joined to these tables in order to pull this information. Just to propose a simple example, lets say you have something like an 'Orders' table. Is it possible that sometimes an 'order' will be with an individual, and sometimes it'll be with an organization? Well, you better plan for that. And while you're doing so, consider things like what would happen with the integrity of your Orders records if an individual moved ... either into an organization, or switched organizations ... and remember that this could even potentially happen during the actual process of 'order fulfillment'...
Literally, 'properly' building address/contact information tables for both organizations and individuals, that are truly as flexible as they may end up needing to be, typically involves a whole bunch of tables, and a whole bunch of planning. And whether or not this piece is done right in the beginning often dictates whether or not an entire system remains viable in the long-term sense, so it makes sense to *really* think out this piece. Depending on what your db is modeling out, it's entirely possible that this data can become (even if it doesn't start out seeming to be) the most critical piece of the entire system.
Trust me, you don't want to make any serious mistakes in the design of this particular structure ... always account for the impact of all the possible changes that can and DO happen ... out there in the real world.
February 11, 2009 at 12:51 pm
Grant Fritchey (2/11/2009)
I would go with a single Address table and then join tables to the different entities that use it. This allows you to have a single source for address validation and updates, making maintenance easier. Yes, you'll have to join two tables to get data instead of one, and you should put individual items like suite number into the join table, seperate from address, meaning you'll always have to join across those tables, but it really will make the long term viability of the data much better.
I agree with this. Although I had never considered putting the Suite or Apartment number in the join table, I had always considered that part of the address. Grant's idea is probably a better design. Putting 100 Main Street in the addresses table and Apartment 310, etc... in the join table really makes more sense.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 11, 2009 at 10:08 pm
I've implemented a single address table solution before. One BIG issue that we ran into was changes to addresses (either typographical or just because people move).
When a single address is joined to multiple tables, and belongs to multiple entities, you can't just update a record. If someone is listed as being at 1234 Main Street, and they contact you for a change to say they live at 1235 MainE street, you can't update the address unless you first establish that only one entity is using it. Why? Other entities may still be using 1234 Main Street as a VALID address. The solution when other entities are also using an address is to create a new row with the "corrected" address, and leave all other entities pointing to the same row until you can confirm that they should also change.
You should also put some work into standardizing formats and abbreviations for street addresses, because sometimes you DO want to change everyone assigned to 1234 Main Street. Only some of them are assigned to 1234 Main ST, not street.
A single address table pays some real dividends, but think hard about these kinds of issues before proceeding.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 11, 2009 at 10:36 pm
Grant Fritchey (2/11/2009)
--------------------------------------------------------------------------------
I would go with a single Address table and then join tables to the different entities that use it. This allows you to have a single source for address validation and updates, making maintenance easier. Yes, you'll have to join two tables to get data instead of one, and you should put individual items like suite number into the join table, separate from address, meaning you'll always have to join across those tables, but it really will make the long term viability of the data much better.
I think single table is more efficient way to handle the data As Grant said. Yes one more thing, You can specify your own flag to identify what type of Address you need irrespective of owner ID. The benefit by using a flag is you can logically divide your data into two separate sub tables.
Cheers!
Sandy.
--
February 12, 2009 at 1:49 pm
[font="Verdana"]I go with the separate Address entity (table, dimension).
One advantage is that you can make sure the address complies with any necessary mailing rules (for example, Zip codes in the US, Postal codes in the UK or here in little New Zealand, address formatting) and only have to worry about dealing with all of this in the one entity.
I don't really see an advantage of having separate tables. You could have a flag field to say which parent the address corresponds to if you wish, but I'm not exactly sure I see even the benefit of that, as you will be joining on an ID field of some sort anyway I would think. Also, if you have a flag saying "I correspond to an individual/organisation", what happens when it's both? What does that mean? What happens when it's an address shared across multiple people?
One thing I considered was loading up a Dimension of local addresses with every single possible address (doable for a small country like New Zealand), and then just switching people/organisations between the addresses.
[/font]
February 12, 2009 at 1:57 pm
I'll go with the single-table solution, with specifics beyond the base-address in the join tables (suite/appt number, etc.), as well as having types in the join table (billing, residence, legal, etc.), and specifics like "care of" in there as well.
I also like the idea of keeping a history, again in the join table or an audit log of the join table.
The idea of never updating addresses, but replacing them instead, is a good one. Just make sure you have adequate control over not entering duplicates into the address table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 2:24 pm
The idea of never updating addresses, but replacing them instead, is a good one. Just make sure you have adequate control over not entering duplicates into the address table.
That's why address standardization routines are needed. You have to be sure that when you get
"123 W. Carlisle Avenue", it gets stored as "123 W Carlisle Ave".
The good news is that if you are willing to pay the price, you squeeze a LOT of fat out of your tables.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 12, 2009 at 10:05 pm
Bob Hovious (2/11/2009)
--------------------------------------------------------------------------------When a single address is joined to multiple tables, and belongs to multiple entities, you can't just update a record. If someone is listed as being at 1234 Main Street, and they contact you for a change to say they live at 1235 MainE street, you can't update the address unless you first establish that only one entity is using it. Why? Other entities may still be using 1234 Main Street as a VALID address. The solution when other entities are also using an address is to create a new row with the "corrected" address, and leave all other entities pointing to the same row until you can confirm that they should also change.
I can see other way of data. I mean to say if you wants to make your address more specific or markable you can choose Address Column as XML Type. The Most benefit is that you can easily query as per your requirements and also easy for your DML statements.
Cheers!
Sandy.
--
February 13, 2009 at 1:44 am
Well thanks!
Interesting views here and interestingly not a clear consensus. I had imagined I would just get 'go with plan A' , 'agree plan A', 'definitely plan A but watch for ...' . It has certainly given me food for thought and has challenged my original intention of a single table.
Thanks again
Allen
February 13, 2009 at 7:30 am
Sandy,
I don't believe you understood my point. The datatype has nothing to do with it. The problem is that sharing a single address between multiple entities complicates maintenance, because a move or correction by one entity may or may not be applicable to all. Let me spell out an example.
You and I both work for the No Red company in the company headquarters in Smallville, USA. There is one mailing address in our address file. It has an addressID of 01.
No Red Inc.
6345 Sample St
Smallville, USA
In the Personnel table of our system, we each have 01 in the addressID column. In the Organization table, our company has an addressID of 01, as do any departments located at the headquarters. Everything points to that one address and we are somewhat normalized. So far so good.
Now, because of devotion to duty above and beyond the call, you are now being promoted to a new divisional office being opened up in Metropolis. We need to change your mailing address to:
No Red Inc.
1999 Commerce Ave
Metropolis, USA
We can't just go to the '01' address and update it, because that would also change the mailing address used by me and the rest of the departments at our headquarters in Smallville. That address is still valid and must be left alone. Instead, what we have to do is create a new address row with an ID of 02, put the Metropolis address in there, and then change the addressID in your personnel row to point to the 02 address.
Now, as I re-read the original question, we don't necessarily have to avoid duplication of addresses. If the address table has a separate row for every person and entity and no shared addresses, then the situation described above is a non-issue.
I hope that clears things up.
Bob
P.S. Allen, you are welcome. If my posts complicated this beyond where you intended to go, please excuse me and just ignore them.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 15, 2009 at 12:26 pm
[font="Verdana"]Bob,
I think most of us who have had to maintain addresses have run into that issue. Here's a breakdown of what I ended up doing to address it (which is similar to what you mention).
For a change of address:
1. Check the if the new address exists in the address table. If not, add it.
2. Move the address reference to point to the new address.
The upside of this approach is that people can share addresses, and then one person can move and not have it effect the other people who use that address.
The downside of this issue is that any addresses that are manually added run the risk of being mis-typed, and then when they are corrected, we end up with an invalid orphan address. However, the bulk of the addresses are likely to be added from existing addresses or from a feed from (her in New Zealand) the post office. So invalid orphan addresses aren't too much of a worry really.
There's no need to keep "one address per person" with this approach. In fact, "one address per person" has its own issues. For example, a postal address, a home address and a work address? Some people may have all three. Others might have just the one. Depending on the context, which address is valid? Which do we store in our "one address per person" scheme?
[/font]
February 15, 2009 at 1:21 pm
Thanks, Bruce.
With the data we're currently working with, I find that the percentage of orphans is more than made up for by the reduction in duplicates.
You're right about the "one address per person". I went to the opposite end of the spectrum and over-simplified. I've worked with a system where the schema was actually to have a separate address table, but its key was a personID and address-type. Which in some respects made me cringe. I don't have the stats any more, but as I recall, close to 50% of the addresses in it were duplicates because they almost always populated both home and work rows... with the same data if need be. Fortunately it was probably only about 50k rows total.
By the way, where are you in New Zealand? My son currently lives in Dunedin.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 15, 2009 at 2:02 pm
Bob Hovious (2/15/2009)
By the way, where are you in New Zealand? My son currently lives in Dunedin.
[font="Verdana"]Auckland. So other end of the country. Dunedin's a nice little town, if a little cold in winter![/font]
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply