I have a project where I have many records of movies, and I need to apply certain genres to them. I could define a field for the genre as a enum, or even just a varchar linked to a table, but I would like the option to have multiple genres per movie. What’s the best way to go about this? There doesn’t seem to be any option for an array, or anything similar, with the implementation of hsqldb in libreoffice being as outdated as it is.
One configuration I’ve tried was making a unique field for every genre as a boolean; true if the movie’s in the genre, false if not. This, however, makes a very wide and overencumbering table, even if it’s separated from the main table (I’m including ~20-30 genres), and a giant array of check boxes seems like a waste of space, and definitely not a good use of relations.
Another configuration I thought about to counteract the repeated use of checkboxes is making a table for every genre. That way I’d only need to store the ID of the movie in each table, making them much more managable; not to mention giving better options for myself in the future if I ever need to include more data per genre. But 20-30 tables seem much worse than 20-30 fields, as it would really just be moving the clutter from inside a table to the entire workspace.
Is there a right way to go about this? I’ve searched all around the web, and haven’t quite found the right answer to this problem. Is there a unique data type I could manually plug in with sql? Is there another table configuration that solves all these problems? I’ve only recently started working with databases, and it seems like I’ve tossed myself into the deep end. Even a confirmation that I’m on the right track would ease my concience. Thanks in advance!