September 26, 2011 at 9:05 am
SwayneBell (9/26/2011)
L' Eomot Inversé (9/24/2011)
Burninator (9/23/2011)
Sorry Evil K – addresses can belong to many users/persons.What gave you the impression that Craig thought addresses belonged to only one person? I couldn't see anything in his post that suggested that.
Tom, I read it that way too
Evil Kraig F (9/23/2011)
Yes. Yes. Yes.Otherwise, each address should belong to one and only one particular user, and not be a lookup.
I'm sure Craig will jump in and clarify
I have to agree with Evil Kraig. In most systems where addresses are just ways to contact people (and not the point of the business like real estate) an address does belong to a single person/account. Now the point here is that you have an AccountID or someother foreign key to your user/account table for any given address. That is NOT to say that you address table has to be unique. As he said you don't want to have your address table be a list of all addresses and used as a lookup table. Yes you may end up with some redundant data but most of the time the ease of data retrieval is well worth the denormalization. This of course does not hold true when addresses play a more prominent role in the business. As will all things sql the "correct" answer it depends on your particular business needs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 26, 2011 at 12:13 pm
Burninator (9/23/2011)
I’m gonna go out on a limb here and disagree with all y’all. I work with address data extensively. I ceased to believe years ago that “we’ll only need one address or phone number, ever” could ever be a true statement. Things change, businesses grow. People have multiple addresses and addresses can belong to multiple people, or entities. Sorry Evil K – addresses can belong to many users/persons. I personally have lived with up to 10 people at a single address. (not fun btw)
I agree. However, that doesn't mean that if *1* person moves, I desire to have to fight the integrity of the system by having to switch them from a 'shared' address to a different 'shared address', that may or may not exist.
If Joe moves, you’re not going to want to just update his address. You’ll want to add a new address and indicate the date when the old address became invalid and when the new one becomes valid.
For shipping addresses, perhaps, since you'll need the historical for auditing if the problem ever arises. It depends on the nature of the business and it's much easier to allow for a 'valid from - valid to' scenario if each address in the table belongs to one person. In your case here, the 10 ppl sharing a flat, I would have that address in the table 10 times as an extended attribute of the 10 people, not a discreet entity that was associated to each of the 10 people.
If you mailed something to Joe two years ago, but only had his current address, you’d never be able to tell him where his stuff went two years ago.
Nor, 95% of the time, do you care. All you usually care about in this case is sending it to the proper one. There are exceptions and in that case you're correct, a valid from-to scenario is fine. This has nothing to do with bringing addresses up to 5th/6th normal form, it's added functionality to the methodology.
A reasonable amount of normalization is a wonderful thing in this regard, besides people are not the only things that have addresses, companies have addresses, real property as mentioned in the prior post, etc.
Yep, and an address as a legal entity (also known as a plot, and any other number of terms, but you already know that. I bring it up to show there is a way to easily signify the difference even in your own application for intuitive use) are exceptions to the general concepts of using addresses. Most people use addresses as a dimension, not as a fact. Real Estate (your next paragraph) changes the rules on that and addresses are now entity descriptors, if not the actual keys.
Now I freely admit that this is probably the most annoying structure for the UI developers, but hey, the data is nice and consistent.
Forget UI, I'm more worried about the DB code. I can make anything pretty in a proc for the front end guys. You are freely associating lookup data to multiple people who are for more transient in that lookup data then I usually prefer.
My usual build (NOT for real estate, however):
User:
UserID (Surrogate PK)
BusinessKey (if reasonable... if I'm selling them candy bars I DON'T need their SSN...)
Name
Address: (The UserID/AddressType usually forms the first of the secondary primary keys, and will most likely be my clustered indexing)
AddressID
UserID
AddressType (Usually a choice of 3, Mailing Address, Shipping Address, and Billing Address)
Address Stuffs
(The following if necessary)
AddressValidFrom DATETIME DEFAULT ( GETDATE())
AddressValidTo DATETIME DEFAULT ( DATEADD( yyyy, 100, GETDATE()) )
Historical BIT DEFAULT (0)
-- At this point I would partition the table on AddressValidTo and put anything with an AddressValidTo from earlier then last Sunday (my randomly decided maintenance window) into a historical component, or use the Historical Bit.
In this way, each user can have multiple addresses without an issue. If you need point in time activity you can do so.
The primary item here is that these addresses are nothing more then extended attributes of a single entity (the User), rather then being entities in their own right, which are then associtated to users. Does this create duplication? Yes. Depending on where your volume of work is, researching an address change can be a cost you don't want to incur. The index structure on an address table is tight enough to nearly guarantee seeking, so that's rarely the concern.
I'm sorry you feel that disagreeing with me is a problem. I agree, in real estate, an address is an entity. It can also be an entity for anyone who works with properties as something that has things occur to it. Think Landscaping, Pest Control, or Crime Reporting. These things have events that don't belong to the person occupying a place at the time, but the place IS what the event occurred for. In this case, I can agree with coding using Addresses as entities. Until you need to do work, tracking, and data control for a property though, in most cases you want to avoid it.
[EDIT: A couple of typos and a bad phrasing]
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2011 at 12:18 pm
Sean Lange (9/26/2011)
SwayneBell (9/26/2011)
L' Eomot Inversé (9/24/2011)
Burninator (9/23/2011)
Sorry Evil K – addresses can belong to many users/persons.What gave you the impression that Craig thought addresses belonged to only one person? I couldn't see anything in his post that suggested that.
Tom, I read it that way too
Evil Kraig F (9/23/2011)
Yes. Yes. Yes.Otherwise, each address should belong to one and only one particular user, and not be a lookup.
I'm sure Craig will jump in and clarify
I have to agree with Evil Kraig. In most systems where addresses are just ways to contact people (and not the point of the business like real estate) an address does belong to a single person/account. Now the point here is that you have an AccountID or someother foreign key to your user/account table for any given address. That is NOT to say that you address table has to be unique. As he said you don't want to have your address table be a list of all addresses and used as a lookup table. Yes you may end up with some redundant data but most of the time the ease of data retrieval is well worth the denormalization. This of course does not hold true when addresses play a more prominent role in the business. As will all things sql the "correct" answer it depends on your particular business needs.
+1, a much simpler way to answer it. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2011 at 2:18 pm
Burninator (9/23/2011)
I’m gonna go out on a limb here and disagree with all y’all. I work with address data extensively. I ceased to believe years ago that “we’ll only need one address or phone number, ever” could ever be a true statement. Things change, businesses grow. People have multiple addresses and addresses can belong to multiple people, or entities. Sorry Evil K – addresses can belong to many users/persons. I personally have lived with up to 10 people at a single address. (not fun btw)If Joe moves, you’re not going to want to just update his address. You’ll want to add a new address and indicate the date when the old address became invalid and when the new one becomes valid. If you mailed something to Joe two years ago, but only had his current address, you’d never be able to tell him where his stuff went two years ago.
<snip>
I think you are talking about two different things here, but perhaps I'm misunderstanding you.
Assuming that Address and Person are two separate entities:
1. If a person changes addresses (one or more addresses) then the temporal-ness of that information is about a change in the Person. The address hasn't changed so in that regard, there is no need to track temporal information about the address.
2. However, if it is important to know when an address changes (say for the zip code change mentioned previously), then you'd want to track that change in a temporal manner.
But, it sounds like we are getting into data warehousing territory versus transactional processing. Also it seems to fall heavily on the actual requirements. I’ve been given some really odd requirements before. But, after talking with client to understand what they are asking for, sometimes they make sense on how the Business does its business and not how we would model something in the “real world.”
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply