0

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?

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)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.