How to multi-match two tables efficiently?

Question

I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both tables have multiple columns. The problem is, that every object may belong to an indefinite amount of categories and I need to query both efficiently: All objects that belong to a specific category and for any object to which categories it belongs, which is why I can't just use joins.

My idea was to create a third table with just two columns, matching objects to categories. But it looks like an ugly solution to me and I also don't know what indices I should set upon it, if any at all. I might end up with the key column's data being saved four times: In the original table, in its PRIMARY KEY, in the matching table, and in the index on that table.

Is there a better approach or maybe even a standard method to handle such situations?

Answer 1

3-table scheme is the most compatible. 3rd table will look like:

CREATE TABLE jointable
(
    obj_id, 
    cat_id, 

    PK (obj_id, cat_id), 
    FK (obj_id) REF obj (id), 
    FK (cat_id) REFcat (id)
);