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 implemente开发者_如何学运维d custom analytics before so would feel reassured with feedback.

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.