Database Design – Schema for Storing Historical Data on Shared Identifiers

database-designschema

When user account is assigned an IP address, I create a table entry for the account/IP mapping. This data in the table is updated every 5 minutes. Right now I'm only keeping real-time data but I need to figure out how to store this historically.

So let's say at 09:00:00 I have the following:

10.10.100.20   dom\jsmith
10.20.5.30     dom\psanders
10.90.220.5    dom\kwest

This all stays the same for the next 45 minutes and then at 09:50:00

10.10.100.30   dom\jsmith
10.10.100.20   dom\jsmith
10.20.5.30     dom\bpeters
10.90.220.5    dom\sparker

In this case jsmith logged into a term server so his account maps to a second IP. The more difficult problem for me is bpeters shares a desk with psanders and the same for kwest and sparker.

Goals:

  • Recall who was logged in on 8/10 at 9:00.
  • Recall what IPs did jsmith have between two dates/time periods
  • Recall who was assigned a given IP on a given date

I thought about creating a table for each time period but over a long period of time that's a lot of tables and probably really inefficient. The number of IPs to track is probably 20k and individual accounts 15k.

As a novice to DB engineering I don't know if I'm missing a simple solution here or if I should invest a good amount of time into DB schema design.

Best Answer

Shouldn't be too difficult, the below assumes you are using SQL Server. My design would be to have a table called "CurrentLogOn", which would look like:

LogOnID (PK - AutoIncrementKey)
UserName
IPAddress
LogOnTime (DateTime, default to GETDATE())
LogOffTime (DateTime, no default)

You would then want a second table called "HistoricalLogOn", which would look similar to the first. The reason to separate the tables would be so CurrentLogOn table operates with the smallest number of records for performance reasons.

HistoricalLogOnID (PK - AutoIncrementKey)
LogOnID
UserName
IPAddress
LogOnTime (DateTime, default to GETDATE())
LogOffTime (DateTime, no default)

I would then place a trigger on the CurrentLogOn table. The trigger would be an INSERT trigger (would trigger when a record is inserted) and would look like:

CREATE TRIGGER UpdateUserLogOn
AFTER INSERT
AS
UPDATE CurrentLogOn
SET LogOffTime = GETDATE()
WHERE UserName = (SELECT UserName FROM Inserted)
AND LogOffTime IS NULL
END

This should update the "old" logon every time the user logs in again. Note that this assumes a record is added to the table each time a user logs on.

Lastly, at the end of the day or at some point during the day, you will want to move all "old" data into the history table. To do so you could run a job that does two operations:

INSERT INTO HistoricalLogOn (LogOnID, UserName, IPAddress, LogOnTime, LogOffTime)
SELECT LogOnID, UserName, IPAddress, LogOnTime, LogOffTime
FROM CurrentLogOn
WHERE LogOffTime IS NOT NULL;
GO
DELETE FROM CurrentLogOn
WHERE LogOffTime IS NOT NULL;
GO

This last part will insert the old records into the history table and subsequently delete them from the Current table.

Related Question