CODEWITHSUNDEEP
sqldatabase-design
Lex
Lex

Reputation: 5014

Basic database schema for analytics

I'm writing a small bit of code to record the users actions on a Kiosk, and would appreciate feed back on my intended SQLite schema.

Basically I'm trying to mimic Google Analytics. The kiosk is very similar to web page analytics, but I'm including an action of "onScreenInteraction". Where users can interact with products or information, and we want to record which information was accessed.

A user session table would look like

* id :int
* kioskId :int
* startTimeStamp :time
* endTimeStamp :time

And the user action table would look like

* sessionId :int (FK)
* actionStart :time
* actionEnd :time
* actionType :varchar
* actionDetail :text

this should hopefully record all the details required to chart graphs, feel free to tear it to shreds if I'm way off track. I haven't implemented custom analytics before so would feel reassured with feedback.

Upvotes: 0

Views: 783

Answers (1)

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51553

Your user action table allows for only one action. There needs to be an id on the user action table.

How do you identify a user? You're not likely to get the endTimeStamp, as a kiosk user can just walk away.

How are you getting the actionEnd time stamp?

Otherwise, your table design looks like you'll be able to get counts of users and actions. I'm not so sure your time stamp data will be accurate enough.

Upvotes: 1

Related Questions