Quite often at work we'll have a scenario where we have an item in the database that we need to assign multiple staff members to. Normally we do this assigning by creating a new table with two columns, the first being the items ID and the second the Staff member's ID so when we want to either look at all the items assigned to a staffmember we can query the DB by staff ID or if we want to see all the staff assigned to an item we can query by the item's ID.
That's a little confusing so here's an example. Say there's a table full of movies in our database, each having an ID, a title, description, etc. For whatever reason the boss wants to know who likes each movie. This'd be fine if only one person could like each movie as we could just make another column in the table but as it stands multiple people can like the same movie which makes it a bit difficult. What we'd do now is create another table that has only two columns, the movie ID and the staffID.
This all seems to work but I'm really curious if there's a better way, or even an alternative way in designing the database. I can't think of one but I have a niggling feeling we're not really following best practices. If we are and it's all good feel free to ignore this question but I'd love some opinions from all the seasoned pros out there!
Please be gentle on me - I only started programming in .net/SQL (from scratch in a business environment!) about 6 months ago so I don't know a lot about this sort of stuff. :)