Hello,

I would like to know what suggestions anyone has for this, I expect pretty common, problem.

I'm creating a number of tables which need to store the history of particular objects. For example, lets say a people table like this:

PersonId (PK) | Name | PhoneNumber | DateFrom (PK) | DateTo

Every time a person changes their name or phone number, a new row is inserted and the relevent date put in. This means a persons details are available for any point in time.


The issue I have is how to deal with the PersonId in relation to foreign ids and the creation of new personids. I know of three solutions:

1. Make another almost identical table, except without the date information. This table will store the most recent entry and there will be one row for each person.

2. Have another table that has just one column of the PersonIds.

3. Have no extra tables.


1 and 2 allow for easy foreign key constraints and also easy creation of new personids. 3 is the most simple structurely, but I'm worried there wont be any foreign key constraints or at least it will be harder to create them. Also, to create a new id for 3 I'd guess I'd have to do a query something like this:

INSERT INTO Persons(PersonId,Name,PhoneNumber,DateFrom,DateTo)
(SELECT Max(PersonId) FROM Persons)+1,
'Joe Bloggs',
07777777777,
getdate(),
'1/31/9999'

I think I'm leaning towards 3 at the moment, perhaps looking into how to make constraints that will stop the submission of wrong data. Otherwise I'll just to reply on my front end manage that.

As a further note, history is just as important to me as present details.

I have discussed part of this problem also here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103468

Please let me know your thoughts on this. I really appreciate the help and time people put in on this forum, so thanks!

*thinking out loud*
One table only.
Composite primary key: employee identifier and datestamp.
Create a view for current/latest record for use in JOINs

*shrug*

Yep, that's kind of what I was thinking. Just worried about constraints and creating new ids, but don't think that should be too big an issue. Just want to make sure there's not some big flaw in doing it that way.
Cheers

What constraints are you worried about?
Can you explain a little better what you mean when you say "creating new ids"?

When a new person is added a new PersonId will be needed. That's what I meant by creating new ids. In a table where I'm not worried about history I often you'll use use an identity column for an Id, when the creation of Ids is then handled for me.

The constraint I was worried about is when I want to reference an object in another table. Say I make a table "cars" where one person can own several cars so PersonId is one of the columns. Usually that column would be constrained as a foreign key of the people table, but as the primary key of the people table is PersonId and DateFrom (or datestamp) a foreign key constraint wont be correct.

You need 2 tables for a person e.g. Persons(PersonId) and PersonDetails(PersonId references Persons,DateFrom,DateTo,Name,PhoneNo).

Table Cars would reference table Persons.

Table Persons should contain any "constant" information about a person e.g. Date of Birth.

I guess that's a fourth method then; another table that contains the objects constants and then another table with history. So for people one table would be People and the other PeopleDetails, the latter storing changing attributes.

Only issue is, what if there are no constants? Then it will be only store an Id and be the same as solution 2. But is this a good idea to have a table of only Ids?

If there are no constants then table Persons will just have the one column PersonID - that's perfectly valid.

Okay. That's actually something I've already tried. The thing I didn't like is that I was using the name "People" for a table with just Ids even though that table will usually not be referenced, and resulting history table name (PeopleDetails, PeopleChanges or something like that) seems a bit abstract and doesn't represent an object in it's own right.

Or maybe I'm just thinking too much about names. I always get obsessed with naming!

I have used option 1 successfully many times.
It is simple, allows full point-in-time reporting, and will give you better query performance for 90% of your transactions.

It's good to think about names! In this case, each People row represents an object in its own right - a person. Each PeopleDetails row also reprsents an abstract object in its own right - the details that pertain to a particular person during a specified date range.

Each car belongs to a person, not to the details of a person.

Blindman's preferred option 1 is the same thing, but with the current PersonDetails info duplicated into the Person table for convenience/performance.