Mechanism for differentiating between "by me" and "liked by me"

[simonw]

Some of the content I'm indexing is by me - photos I've taken, tweets I wrote, commits, comments I posted.

Some of it is stuff that I've "liked" or "bookmarked" in some way - favourited tweets, Pocket articles, starred GitHub repos.

It woud be useful to be able to differentiate between the two.

[simonw]

Some opet questions about this:

• Should I restrict to two exclusive categories here, or should I have a generic category mechanism that can be expanded to more than two?
• Should an item be able to exist in more than one category? Do I want to be able to mark an indexed item as both by-me and liked-by-me for example? This question is more interesting if the number of categories is greater than two.
• How should this be modeled? Single column, multiple boolean columns, JSON array, m2m against separate table?
• What's the best way to make this performant

[simonw]

Relevant: https://charlesleifer.com/blog/a-tour-of-tagging-schemas-many-to-many-bitmaps-and-more/

SQLite supports bitwise operators Binary AND (&) and Binary OR (|) - I could try those. Not sure how they interact with indexes though.

[simonw]

Relevant post: https://sqlite.org/forum/forumpost/9f06fedaa5 - drh says:

Indexes are one-to-one. There is one entry in the index for each row in the table.

You are asking for an index that is many-to-one - multiple index entries for each table row.

A Full-Text Index is basically a many-to-one index. So if all of your array entries really are words, you could probably get this to work using a Full-Text Index.

[simonw]

I'm going to implement the first version of this as an indexed integer category column which has 1 for "about me" and 2 for "liked by me" - and space for other category numerals in the future, albeit a row can only belong to one category.

I'll think about a full tagging system separately.

[simonw]

I'll make category a foreign key to a categories table so Datasette can automatically show the name column.

[simonw]

Default categories:

• 1 = created
• 2 = saved