What is the proper way to configure a set of many similar attributes for a record in Base?

[Nathonion]

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!

[Ratslinger]

Hello,

This is done with movies in one table and a second table for all genre.
.
The second table is linked to the first table by ID in the Movie table. A third table is created with all possible genre (can add to this any time). This list is used in the Genre table. Now to keep from having duplicate genre for one movie, a unique index is created in the Genre table for the Combined fields of MOVIE_ID and GENRE (edit MOVIE_GENRES table in sample attached and select Tools->Index Design... from Menu):

.
Sample → MoviesForum.odb (13.0 KB)
.
Base documentation may help → https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide
.
On Line help → LibreOffice Database

[Nathonion]

I suppose I had thought of that solution in some sense, but had been wary of having multiple records of the same movie in the movie_genres table; my mind was somewhat convinced that relationships with the movies had to be one to one. I understood that you could have a one to many relationship, but thought it would be better if the movies were one to one everywhere, seeing as they are the focus of the database. But a table about the relationship between movies and genres clearly should be about said relationship, and not just an extension of the movies table. A table should be made up of unique records for the purpose of the table only, not for the purpose of some other table, making the idea of multiple ids from another table a valid and worthwhile idea; not to mention the fact that these external ids are backed by their single unique origin in the other table. Makes perfect sense in hindsight, and it took your example for me to get a hands-on idea behind this.

Applying this logic to your example, you designate the genres table specifically to the said genres, because the table is ONLY about the genres. I suppose if you were to have extra fields that relate ONLY DIRECTLY to the genre, you could put them in this same table (which solves that other problem I had; handy!). Then in the movies table you have a similar setup to the genres table: records for ONLY movies, and fields related ONLY DIRECTLY to the movies. And finally, the movie_genres table is ONLY about the relationship between movies and genres, so each relationship gets a unique record. Now I have a solid metric to decide whether certain data should go in a certain table.

This answer was very good, and I really appreciate it. It solved my question, yes, but more importantly showed me the correct thinking process to follow from now on to keep organized and meaningfully related databases. Thanks a ton!