Structuring SQLite database with 2 relationships

javasqlite

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?

Best Answer

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.

Related Solutions

Using desktop computers to manage an SQLite database on a fileserver

The issue with this is that as you scale SQLite, you run into the fundamental problem that it doesn't have row-level locks, only table locks. The "thing" you run out of first isn't a raw resource like CPU or IO or memory - it's that your app will get bogged down in lock management. You can have lots and lots of users sharing an SQLite database, but only your readers can tolerate waiting for your writers. If this database is going to be on a a shared drive, then that will only make your scalability problems worse as it introduces additional latency, making the lock acquire/release cycle longer!

Also, in a business, there is no such thing as "non-critical data" unless you are literally talking about people MP3 or recipe collections! If people use it to get their work done and for whatever reason it "goes away" then they will wonder why, and perhaps escalate it up their reporting line. I totally sympathize with how long it takes to get a "proper" database server (be aware that the DBA feels exactly the same way, when he's waiting for the procurement department to buy the servers!) but unless you are prepared to support something forever, don't deploy it. Or if you do, develop it with something like the free versions of Oracle or SQL Server, so that when the time comes, it can easily be migrated into the datacentre.

SQLite/Android – First Time Using an Index

WHERE c = ?

This lookup can be optimized with a simple index on the c column:
```
CREATE INDEX T_c on T(c);
```
WHERE a LIKE ? || '%'

There are several restrictions on indexes that can be used for LIKE searches. In this case, you need a case-insensitive index:
```
CREATE INDEX T_a_ci ON T(a COLLATE NOCASE);
```

WHERE b LIKE ? || '%'

same here:

CREATE INDEX T_b_ci ON T(b COLLATE NOCASE);

WHERE a LIKE ? || '%' AND b LIKE ? || '%'

The database can use at most one index per table in a query, so it will use one of the indexes above. If you have run ANALYZE, it might be able to choose the better one.

To check which indexes are actually being used, use EXPLAIN QUERY PLAN.

Whenever you change tables, any indexes must be updated too. When you're filling an empty table, it is usually a better idea to create indexes afterwards.

When your application does many updates to some table, it might be worthwhile omitting some index(es) that are useful only for seldomly-used queries.

Best Answer

Related Solutions

Using desktop computers to manage an SQLite database on a fileserver

SQLite/Android – First Time Using an Index

Related Question