Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #171 > unrolled thread

Re: Historic values in LI

Back to article view | Back to comp.lang.java.databases

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: Historic values in LI "Arved Sandstrom" <arved.sandstrom@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: Historic values in LI "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: Historic values in LI "=?ISO-8859-1?Q?Arne_Vajh=" <=?iso-8859-1?q?arne_vajh=@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
      Re: Historic values in LI "Arved Sandstrom" <arved.sandstrom@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
        Re: Historic values in LI "=?ISO-8859-1?Q?Arne_Vajh=" <=?iso-8859-1?q?arne_vajh=@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
          Re: Historic values in LI "Arved Sandstrom" <arved.sandstrom@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000

#171 — Re: Historic values in LI

  To: microsoft.public.dotnet.l
"Nils Magnus" <nmee@nmee.net> 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

[toc] | [next] | [standalone]

#172

  To: microsoft.public.dotnet.l
Arved Sandstrom wrote:
> This is obviously open to debate. It's merely a system I might consider had 
> I to implement your requirement.

This type of data modeling cries out for a standard data-warehouse star 
schema, with fact and dimension tables.

-- 
Lew

---
 * 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

[toc] | [prev] | [next] | [standalone]

#173

  To: microsoft.public.dotnet.l
Arved Sandstrom wrote:
> {X-posted to cljd for extra comment]

Why cljd and not any other database group ??

Arne

---
 * 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

[toc] | [prev] | [next] | [standalone]

#174

  To: microsoft.public.dotnet.l
"Arne Vajhoj" <arne@vajhoej.dk> wrote in message 
news:484bf28c$0$90273$14726298@news.sunsite.dk...
> Arved Sandstrom wrote:
>> {X-posted to cljd for extra comment]
>
> Why cljd and not any other database group ??
>
> Arne

Well, it's the only database-oriented NG I follow, and I thought that 
getting the subject into at least one such NG would be useful.

In hindsight it's a topic that, suitably reworded to make it somewhat more 
encompassing, would be interesting in a more general database-oriented 
group.

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

[toc] | [prev] | [next] | [standalone]

#175

  To: microsoft.public.dotnet.l
Arved Sandstrom wrote:
> "Arne Vajhoj" <arne@vajhoej.dk> wrote in message 
> news:484bf28c$0$90273$14726298@news.sunsite.dk...
>> Arved Sandstrom wrote:
>>> {X-posted to cljd for extra comment]
>> Why cljd and not any other database group ??
> 
> Well, it's the only database-oriented NG I follow, and I thought that 
> getting the subject into at least one such NG would be useful.
> 
> In hindsight it's a topic that, suitably reworded to make it somewhat more 
> encompassing, would be interesting in a more general database-oriented 
> group.

microsoft.public.sqlserver.programming
microsoft.public.dotnet.framework.adonet
comp.databases.sqlserver

were some of the other possibilities.

But it is not obvious exactly where the questions belongs.

I was just puzzled that you picked the Java one.

Arne

---
 * 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

[toc] | [prev] | [next] | [standalone]

#176

  To: microsoft.public.dotnet.l
"Arne Vajhoj" <arne@vajhoej.dk> wrote in message 
news:484c8a3f$0$90275$14726298@news.sunsite.dk...
> Arved Sandstrom wrote:
>> "Arne Vajhoj" <arne@vajhoej.dk> wrote in message 
>> news:484bf28c$0$90273$14726298@news.sunsite.dk...
>>> Arved Sandstrom wrote:
>>>> {X-posted to cljd for extra comment]
>>> Why cljd and not any other database group ??
>>
>> Well, it's the only database-oriented NG I follow, and I thought that 
>> getting the subject into at least one such NG would be useful.
>>
>> In hindsight it's a topic that, suitably reworded to make it somewhat 
>> more encompassing, would be interesting in a more general 
>> database-oriented group.
>
> microsoft.public.sqlserver.programming
> microsoft.public.dotnet.framework.adonet
> comp.databases.sqlserver
>
> were some of the other possibilities.
>
> But it is not obvious exactly where the questions belongs.
>
> I was just puzzled that you picked the Java one.
>
> Arne

After a bit of research I figure comp.databases looks fairly promising. I 
picked three likely candidates and subscribed. My newserver only pulled 5 
posts for alt.comp.databases, so that's out. comp.database looks moribund - 
no posts at all. comp.databases looks pretty healthy, and the threads have 
the right kind of questions.

I did add a few newsgroups for SQL Server and PostgreSQL, which are the 2 
servers I use the most at present.

I figure a person would probably have to keep track of 5-10 DB-oriented 
groups in order to pose some questions. If you had a question which boiled 
down to:

1. How do I best design the logical data model for this problem?
2. How do I set it up for database X (DDL questions, say)?
3. I am accessing X with language Y - what's the best way to do 
such-and-such?

that might very easily start out as one medium-length post in one NG, likely 
language *and* DB specific, but in hindsight best be handled as 3 separate 
questions in 3 separate groups.

It's the old problem: how many NGs do you follow? :-)

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web