Time series and unique users

Question

I need to collect user data every few seconds where each user is unique. I've done some research and concluded that I should consider my data as time series data because I'm looking at how user behaviour changes over time. I want to use DynamoDB and in its best practices for time series doc, it describes creating a new table for each day, hour, minute, depending on convenience. In the example tables shown in the article, the partition key is the new-table-interval (e.g. the date) while the sort key is a smaller interval within it (e.g. seconds).

What I'm confused about is how to implement unique user ids in the database since I believe that every query made will be based on the user the data is for. Would it be wise to use a secondary index which includes the user id as a primary or sort key?

Answer 1

As others have mentioned I think you are getting confused about where the user id needs to be unique. In it's most simple form i would have 2 tables.

1. A table for users where their user id is unique
2. A table for the time series. (where the user id is repeated).

Below is a text example I create which will run on SQL server to illustrate the point.

CREATE TABLE [dbo].[User](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [UserName] varchar(50), 
     ) 

CREATE TABLE [dbo].[TimeSeries](
    [TSidx] [int] IDENTITY(1,1) NOT NULL,
    [UserId] int, 
    [DT] [datetime] NULL,
    [Val] float ) 

insert into [User] ([UserName]) Values ('Jim') 
insert into [User] ([UserName]) Values ('Bob') 

select * from [User] 

-- Run these below a few times each

insert into [TimeSeries] ([UserId], [DT], [Val]) select [UserId], getdate(), Rand() from [User] where [UserName] = 'Jim'
insert into [TimeSeries] ([UserId], [DT], [Val]) select [UserId], getdate(), Rand() from [User] where [UserName] = 'Bob'

Select * from [TimeSeries]

Identity columns in SQL should be unique, but you could enforce this by defining each as the primary key.

Alter table [User] add constraint Userpkey primary key ([UserId])
Alter table [TimeSeries] add constraint TSpkey primary key ([TSidx])

If you do this, you can then enforce the relationship between the 2 tables using a foreign key constraint.

Alter table [TimeSeries] add foreign key ([UserId]) references [User]([UserId]); 

That would stop a record being inserted into the TimeSeries table unless the UserId existed in the Users table.

Answer 2

Unique user IDs make no sense in the time series data, since a user with a particular ID may appear more than once in a period of time.

You might want to constrain the user ID in the time series tables to be a foreign key into a User table.