3

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?

3
  • 1
    I'm not sure what you're asking here. The role of a UserID in a time series would be the same as its role in any other table: identifying the user. The article you linked doesn't even mention user id's. The word "attribute" is apparently being used in the article to mean either "data points" or "anything that is not part of the primary key." May 23, 2018 at 16:15
  • @RobertHarvey I've edited my question to clarify a bit more, not sure if it'll help.
    – rafvasq
    May 23, 2018 at 17:10
  • 3
    OK, well you would add a column for userid. What do you put in there? The userid corresponding to the user for which a particular data record pertains. I think your confusion stems from the idea that user id's will somehow be unique in this table, or that the database will be generating them. Neither of those things is true. May 23, 2018 at 19:29

2 Answers 2

2

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.

2
  • Normally you would include the constraints in the original CREATE TABLE statement (except for one link in a cycle of references), rather than as separate ALTER TABLE statements
    – Caleth
    Jun 11, 2018 at 9:44
  • @Caleth. Agreed. Today: Baby steps... Jun 11, 2018 at 9:50
1

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.

2
  • What I want to collect is individual user data over time, and there would be multiple users. My original idea was to use the userid as a partition key and simply include a timestamp on each entry (maybe as a sort key) but I assumed that this would actually fall under the description of 'time-series data' since every entry has a time-date attached to it.
    – rafvasq
    May 23, 2018 at 17:43
  • Keep it simple. Partitioning is about performance and scalability. Have you scoped out anticipated volumes? Jun 11, 2018 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.