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