How to organize the SQLite DB in my app

Question

At this moment I have a user table which consists of the following fields" id, name, email, sex, password and avatar.

Now, these users will have items, will go to places, will have friends and will do some actions. There will be statistics regarding their activity.

Should I store references for these actions (and the corresponding stats) and store the actions themselves in other tables, or should I just add more fields to this table?

For example, checkins, following, friends - should these be in another table?

How about events?

Someone is friends with another person. Someone follows another person. Someone now has a status of something.

Should these be stored in a table with their timestamps and the corresponding actions related to them (someone liked this post, comments and so on)

but which is the better approach? should I stick to smaller tables or stick everything user-related in one user table? how about the events table - should I put events in a table? — Kaloyan Roussev, Oct 23 '13 at 05:50
I Think concept of RDBMS is good and clear so we should use that! — Manish Srivastava, Oct 23 '13 at 07:30
How would you store multiple events for one user? Multiple records? An event list in one column? Read up on [ER modelling](http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model) and [normalization](http://en.wikipedia.org/wiki/Database_normalization). — CL., Oct 23 '13 at 08:37

Kaloyan Roussev · Accepted Answer · 2013-10-23T10:42:19.713

After some reading and asking around here is what I concluded:

a table for each type of object: users items places

a table for each object's additional data: user info, actions item info place info

a table for each type of action or event likings followings friendships

a table for events

event_id, event_type, parameter1, parameter 2, parameter 3, parameter 4* (*check note below)*

a table for event likes event_id, person_id

a table for event comments event_id, person_id, comment_text

if a user has 13 messages in their mailbox and 8 of them have been read, in the messages table this will be a boolean "read".

Is this looking ok so far?

If I want stats, I will not store them in a record, I will count number of results for some query, eg:

user Kylie has 14 followers because this is the number of rows returned after quering the table for this user

note: The only thing that bothers me is events table, because different types of events require different parameters, e.g.

event type friendship needs two users' ids. event type purchase needs a user and a product id.

so is it okay for me to store all events in one table or should I make multiple tables for each event type?

PS: Im using umlet in order to layout the design and the relations between the tables before I go about implementing them

An answer is not the appropriate place to ask questions. As for the events, these would typically be implemented with inheritance in an OO language. For how to model this in a database, see [this answer](http://stackoverflow.com/a/386683/11654). — CL., Oct 23 '13 at 10:58

How to organize the SQLite DB in my app

1 Answers1