I was recently given an interview question where I was told there is a File table and a Category table. A file (primary key file_id) can have many categories (primary key category_id), and there can be many files. However, there should only be one entry of a file and one entry of a category in their respective tables. Create a schema.
My solution was to create a junction table with file_id and category_id foreign keys. If a user wants to retrieve all the categories of a file, the query would look something like:
SELECT * FROM Files JOIN FileCategory USING (file_id)
Is this solution correct or is there a better way of going about this?
Your solution is correct. The standard way to implement a many-to-many relation between two entities is to create a third entity with a compound primary key consisting of the keys of the two references tables.