Structuring SQLite database with 2 relationships?

Question

I'm current working on a SQLite-database in a android project which will store 3 types of objects; feed, users and posts.

There will be several feeds containing several varying users(a user can be part of serveral feeds) and of my understanding i should implement this by having a Many-to-Many relationship: feedTable - bridge_feeduserTable - userTable

But there will also be posts made by users and a post should be linked to a the user which created the post. How should i link this up?

Should i have a feedTable - bridge_feeduserTable - userTable - bridge_userpostTable - postTable?
or should the posts be stored in a separate database with all the posts? What is the best practice regarding this issue?

Some basic questions that will asked to the database is:
What users are part of feed x?
What posts have been made by the users in feed x?

• ypercubeᵀᴹ: Is every post made by one user and for one feed? Or are there posts that do not belong to a feed?

Answer 1

If I get you right then one post is linked to one user but one user can have many posts. I think this is a one to many relationship. So, put a field user_id in your table post then you know which post is linked to which user.

It's a german page but the first image would help you.

• Rawa: Sorry, i was not clear enough. The bridge_userpostTable will have a user_id and a post_id which ofcourse will enable a query for a user_id (giving ID to all posts). Do you think the bridge_userpostTable is unnecessary and a postTable with user_id column is enough?
• JackTools.Net: Do you think the bridge_userpostTable is unnecessary and a postTable with user_id column is enough? <- If one post is linked to one user then yes. If one post is linked to many users then you need three tables. I UPDATET THE ANSWER
• Rawa: Thanks for the answer, the picture added resolved my question. (: