How implement one-to-many objects database in sqlite for android

Question

I'm quite new to SQLite and SQL and I am struggling with how to approach the following:

My app will display a list of community members. If I click a member, I can see a list of posts made by the members. A post is an object with name, time and message. How can I store this in an SQLite database so that I can query the database by userid and get the list of posts for a specific user.

I have a Users table with these columns:

USER_ID | NAME

I have a Tweet table with these columns:

USER_ID | NAME | TIME | MESSAGE

My questions are: what the best approach / structure to link these two tables? Do I create a new tweet table for every user, or do I store all tweets in one long table with tweets for user 1 first then for user 2 etc?

I'm not necessarily looking for code dumps but rather an explanation of the logic.

Answer 1

An answer has been given and accepted already, but I wanted to add this.

What you want is one table with users, users. In this table you store your user information (user_id, name).

In your Tweets table, store all tweets for all users. One tweet per row. I'm using tweet_id as PRIMARY KEY for the Tweets table.

You can then 'link' the two in code by doing a JOIN like Dave Swersky said.

For example:

Users
--------------------------
user_id   |   user_name
--------------------------
   123    |    'Terry'
   34     |    'Pierre'

Tweets
-----------------------------------------------------------------------
tweet_id |   user_id   |  time      |         message     
-----------------------------------------------------------------------
   0     |    123      | 135646     |  'This is a tweet'
   1     |    123      | 132646     |  'This is another tweet by Terry'
   2     |    34       | 352646     |  'Pierre\'s tweet'

I'm not sure what name is for in your Tweets table. As far as I know tweets do not have a name/subject(?). You do not need to store the user name in both the tweets and users table.

For a quick SQLFiddle, go here: http://www.sqlfiddle.com/#!2/43492/1/0

Join

SELECT u.user_id, u.name, t.time, t.message, t.time 
FROM my_users u
INNER JOIN tweets t ON u.user_id = t.user_id

Answer 2

This is a typical "JOIN" scenario where you have a one-to-many relationship between Users and Posts.

Here is an example of a query that would display all users and their posts:

SELECT u.User_ID, u.Name, p.Time, p.Message
FROM Users u INNER JOIN Posts p ON u.User_ID = p.User_ID

This will produce a resultset with four columns. Each "Tweet" will be displayed with its related User record. The 'u.' and 'p.' syntax are table aliases used to make the query easier to read.