Path: csiph.com!x330-a1.tempe.blueboxinc.net!feeder1.hal-mli.net!border3.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!news-out.readnews.com!transit3.readnews.com!news-out.news.tds.net!newsreading01.news.tds.net!86597e80!not-for-mail From: "Arved Sandstrom" Subject: Re: Historic values in LI Message-ID: X-Comment-To: microsoft.public.dotnet.l Newsgroups: comp.lang.java.databases In-Reply-To: References: Content-Type: text/plain; charset=IBM437 Content-Transfer-Encoding: 8bit X-Gateway: time.synchro.net [Synchronet 3.15a-Win32 NewsLink 1.92] Lines: 92 Date: Wed, 27 Apr 2011 15:22:09 GMT NNTP-Posting-Host: 96.60.20.240 X-Complaints-To: news@tds.net X-Trace: newsreading01.news.tds.net 1303917729 96.60.20.240 (Wed, 27 Apr 2011 10:22:09 CDT) NNTP-Posting-Date: Wed, 27 Apr 2011 10:22:09 CDT Organization: TDS.net Xref: x330-a1.tempe.blueboxinc.net comp.lang.java.databases:171 To: microsoft.public.dotnet.l "Nils Magnus" wrote in message news:eTcBl%23UyIHA.4864@TK2MSFTNGP06.phx.gbl... > Hello, I've just started using SQL to LINQ to access my SQL Server tables, > and it's a wonderful tool. Are there any equally elegant ways to store and > access historic values for the objects? > > For instance, I'm managing a Person table with mail addresses, phone > numbers etc. I would like to be able to store the earlier values of these > fields, so I can see what mail address/phone number a given Person had at > a specific point in time. > > Any tips and links to guides etc. would be very appreciated! > > Thanks. > > Regards, > Nils Magnus {X-posted to cljd for extra comment] As G%ran pointed out there is no explicit support for this in a database, because this historical information is itself data. That is, it's not (in this scenario) metadata, because you specifically stated "so I can see what mail address/phone number a given Person had at a specific point in time." Since you're planning an address book, one thing you've likely already considered is that a Person may have multiple _current_ addresses. In fact, since one Address may also be associated with multiple Persons, you have a many-to-many relationship, usually best implemented as a join table with at least one attribute to describe the "name" of the relationship between that Person and that Address. For example, "summer cottage". For your purposes you could add the date information to that join table. Or more realistically, have fields for start and end dates, but assume that these fields will often be empty. After all, how many people have this kind of data for their contacts? Without some research I couldn't even tell you what *my* addresses have been to an accuracy of a month, let alone exact dates. If doing just the above, you'd then likely want a current flag in that join table also, which would indicate which Person-Address combinations are current. At this stage you'd be able to say, at a minimum, this Person presently has these addresses, but at various times in the past they have also had these addresses. An interesting situation arises at this point - what happens if a Person was once at Address A, then afterwards was at Address B, but now is back at Address A? We've already established that start and end dates may be unobtainable, so distinguishing between "Person-Address A Now" and "Person-Address A Then" cannot depend on those fields. One way around this is to add yet another field to the join table, which would participate along with the Person ID and the Address ID in the join table primary key. I myself would assign no extra meaning to this field, except that it be unique for a given address. At this stage you'd be able to say, at a minimum, this Person presently has these addresses, but at various times in the past they've had these others, and I can see that one of the current addresses was also a past address (for example). Finally, because it very often _is_ quite easy to say, "I don't know exactly _when_ Arved lived at addresses A, B, and C, but I do know that he lived at A, then B, then A again, and now he's at C", you could have one final field in the join table, which is a date sequence number (for lack of a better term). Since re-arrangement operations on this field (changes due to past address inserts etc) will be infrequent, your numbering convention is not critical. If using this scheme you will however have to have a "don't know" value. At this (rather elaborate) stage you could say that this Person currently has these addresses, but at various times in the past they've had these others, one of the current addresses was a past address, and another past address was used twice. Not only that, I know (because of the date sequence number) how long (relatively) the person has had the current addresses, and for 3 of the 5 past addresses I know which came before or after. For phone numbers and email addresses I myself would not use such a detailed system. Strictly speaking both also participate in many-to-many relationships with Person, particularly PhoneNumber, but I myself would be satisfied with the information that such-and-such a Person currently has EmailAddress EA1 and EA2, and in the past has also had EA3 and EA4. So the start date, end date and date sequence fields could be omitted in these join tables. This is obviously open to debate. It's merely a system I might consider had I to implement your requirement. AHS --- * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet! --- Synchronet 3.15a-Win32 NewsLink 1.92 Time Warp of the Future BBS - telnet://time.synchro.net:24