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?