Seeking Relationship Advice

[UncleRobin]

I couldn’t resist the title given my viewing preferences.

Thanks to the patient help of several here, I’ve made some good progress on enhancing the structure of my Viewing Record DB. This is how the relationships look at present:

This is a BIG improvement from the flatfile structure I started with, and the limited normalisation I’ve done so far has already made it cleaner and simpler and made future expansion easier. But now I have design/relationship question I’m stuck on

This is how the “Dramas” table looks at the moment:

I got a lot of practice with UPDATE CASE WHEN tidying up the structure to have COUNTRY, LANGUAGE and NETWORK as FKs, and in removing movies onto their own table, again with LANGUAGE and COUNTRY as FKs. It made more sense to have a separate Movies table because Movies had no entry for NETWORK and their “Eps Watched” and “Eps Total” are of course always the same, at 1 each.

BUT, both Dramas and Movies have Genre columns, and this is where I’m a bit stuck. I’ve arbitrarily decided to assign each Drama or Movie up to 3 genres. As the screenshot shows, those genres can appear in different columns depending on their priority for the given title. I’m hoping to be able to run queries to determine (as a hypothetical example) whether my viewing scores reflect a preference for “Romance Mystery Legal” over “Legal Mystery Romance” etc, and these option are relevant to both Movies and Dramas.

I was wondering whether I need to create 3 copies of the same “Genre” table and name each copy to match the column names in the existing tables then create FK links. Then I wondered if I needed to do something similar with LANGUAGES to allow for content that is in more than one language - an example of this would be Ae Fond Kiss, in which I find the characters much easier to understand when they’re speaking Panjabi/Urdu than when speaking my L1 English, thanks to their Glaswegian accents! Because of that, I’ve been thinking about adding a column for secondary languages in the same content, along the the lines of the Genre options.

And that’s where I’m at now: I’m looking forward to playing around with forms and reports to learn how to get the most out of the structure I have so far, but would REALLY value thoughts and suggestions on how to address these genre/language issues.

[RobertG]

Having a look at the screenshot of the table. This is what I would change there:

“TYPE” appears with the same value very often → create a new table for “TYPE” and set the foreign key into “Dramas”.
Same with “STATUS”. Could be “STATUS” shows only two different contents. Then a boolean field will be enough for this.

Genre1, Genre2 and Genre3 doesn’t seem to be hierachical. So one table “Genre” will be enough for all content here. All the fields could show foreign keys.

[UncleRobin]

Thank you! I knew the normalising wasn’t finished, your suggestions have helped a lot. It’s actually the “TYPE” that could be boolean as there are only two options. “STATUS” currently has 3, with the likelihood of a fourth. And your tips about genre mesh nicely with a suggestion I got from another source:
…
Have a pair of “key pair” tables.

“Drama-Genre”
Drama ID
Genre ID
Priority

“Movie Genre”
Movie ID
Genre ID
Priority
…

Now to put it all together and see how it works. Thanks again!

[cpb]

@UncleRobin,
just an observation:
if “Genre” was not to be prioritised then I would definitely go with the “Drama”.“ID”, “Genre”.“ID” link table.
BUT you do wish to prioritise which means having a 2nd table control on the form and inputting between 2 and 6 values per record.
ALSO you said that you wish to compare viewing scores for the genres “Romance Mystery Legal” over “Legal Mystery Romance”.
you could create the Genre table with 2 fields “ID” and “Genre”, the field “Genre” would contain a unique string made up of 1 to 3 individual genres.
e.g. id: 1, genre: “Romance, Mystery, Legal”. id: 2, genre: “Legal Mystery Romance” id: 3, genre “Mystery” etc.
this way:

1. it’s easy to make comparisons between genre.
2. the form does not require a second table control.
3. 1 to 3 genre are selected with a single keystroke via a list box.
4. no need for a linking table. the table “Drama” will require a foreign key which references “Genre”.“ID”.

below is code which creates the table “tGenre” and fills it with data extracted from your table “Drama” it also inserts a foreign key into the table “Drama” which references “tGenre”.“ID”.
make a copy of your database before executing from menu:Tools>SQL.
I obviously cannot test so any issues let me know.
it’s your database and you decide what is best for yourself, I am simply suggesting that there are alternatives which may be worthy of consideration.

drop table “tGenre” if exists;

create table “tGenre”(
ID int generated by default as identity (start with 1),
“Genre” varchar(100) not null
)
;

insert into “tGenre” (“Genre”)
select distinct
“Genre1” || coalesce(’, ’ || “Genre2”, ‘’) || coalesce(’, ’ || “Genre3”, ‘’) “Name”
from “Drama”
;

alter table “Drama” add “GenreID” int before “Genre1”
;

update “Drama” d set “GenreID” =
(
select id
from “tGenre” g
where
d.“Genre1” || coalesce(d.“Genre2”, ‘’) || coalesce(d.“Genre3”, ‘’) =
replace(g.“Genre”, ', ', ‘’)
)
;

/*
–if/when you feel happy then drop the fields “Genre1”, “Genre2”, “Genre3”
–by executing the code below from menu:Tools>SQL
alter table “Drama” drop (“Genre1”, “Genre2”, “Genre3”);
*/

[UncleRobin]

Thanks VERY MUCH for the coalesce code, that’s going to save me A LOT of time, very much appreciated.

As for the Genres, the linking table with the “priority” field is probably going to be the best option because the example I gave was just that, simply an example. If I end up with a Genre table of let’s say 40 different genres, I want to be able to use any combination of 3-4 of those 40 genres in any order of priority.

This exercise is almost as much about learning DBs as it is about recording my viewing, so I am very grateful for your help. I’m about to start from scratch building a completely new Base DB in LO 7.1.2 portable using Firebird as part of the learning process. When that’s done, I might try again in MySQL or Postgre

When I first had someone teaching me DB basics, decades ago, he was a Delphi nerd and REALLY stressed that DB design starts with pen(cil) and paper. But declining motor skills make that less feasible for me now, so I’m kludging my through from the starting point of a spreadsheet. Now, thanks to the generous assistance of everyone who’s replied to my questions here, I’ve got useful code snippets, I’ve learned more SQL syntax, and I can build a relationships diagram in my “test” DB that will be the onscreen equivalent of a pencil diagram.

[UncleRobin]

I copied the code you supplied, and expected issues with the typographic quotes the Ask LO site imposes. I was not disappointed in that expectation.

After trying a couple of times to replace all the curly quotes, I tried one block of code at a time with replaced quote marks. This enabled the creation of the tGenre table, but when it came to the coalesce code, I am stuck. It was the SINGLE typographic quotes that seem to be causing LO problems. I hope the following screenshot, showing 2 different attempts, illustrates the problems one with the single typographic quotes replaced, one with them left as is:

[cpb]

@UncleRobin,
yes you are correct I just tried copying and pasting code from this site and the quotes were altered.
if code is altered then I guess that makes this site unfit for purpose!

I have uploaded “EmptyDatabase” it contains a single query (the code).
drag and drop your table “Drama” into this database then try the code, it should go through in one shot.

I want to be able to use any combination of 3-4 of those 40 genres in any order of priority.

I do not see that as an issue, you just input each combination once in order of priority.
remember that each combination of genres is unique with an indexed primary key of type integer.
if you have 40 different genres then when using a list box it’s easy scroll to the first occurrence of any particular genre.
comparing genre is quick and easy.
extracting single elements from the string for comparison is not difficult.
like I said in my first post you decide.
EmptyDatabase.odb (3.2 KB)

[UncleRobin]

Wow! Thank you so much for going to all this trouble. Here is what happens when I copy the SQL from the query you wrote and paste into Tools>SQL. First a screenshot of the text copied from the query, to show that I haven’t altered or omitted anything except the commented out portions

Pasting that into Tools>SQL returns this message

On the other point, you said " you just input each combination once in order of priority". Maths was nevre my strong suit, so it’s very likely that I’ve misunderstood this. If I have, please forgive my ignorance.

I assumed that the number of possible combinations would be quite large, so decided to try with a total of 15 unique objects and a sample size of 4

Combination Calculator (nCr) | Combinations Generator

That’s a lot of permutations to enter individually, especially because that assumes that each is a permutation of 4 objects. There would be permutations involving 1,2, and 3 objects each as well, taking the total number to around 35000. Did I understand that correctly?

[cpb]

@UncleRobin,
Using HSQLDB embedded (default database):
I have tested the code and provided that the table Drama (case sensitive) exists then the code goes through in one shot.

000000000000000000000000000000000000000000000000000000000000000

To test viability I created a table and inserted 16 different genre then used this code to generate every possible combination, an effortless exercise:

–qMaxGenreCombinations
select distinct “Genre” “Genre1”, null “Genre2”, null “Genre3” from “tTemp”
union all
select distinct t1.“Genre”, t2.“Genre”, null from “tTemp” t1, “tTemp” t2
where
t1.“Genre” <> t2.“Genre”
union all
select distinct t1.“Genre”, t2.“Genre”, t3.“Genre” from “tTemp” t1, “tTemp” t2, “tTemp” t3
where
t1.“Genre” <> t2.“Genre” and t1.“Genre” <> t3.“Genre” and t2.“Genre” <> t3.“Genre”
order by 1, 2, 3

16 unique genre x 1 to 3 elements = 3616 possible combinations that is 226 records per genre.
if i wish to select a Comedy Western from a list box then I first hit ‘c’ then ‘o’ which brings up the Comedies then I use the slider and scroll to Western, it’s quick and simple.
if it’s essential to have more than 16 unique genre or more than 3 elements then I must agree that this method is probably not viable.

[UncleRobin]

My apologies! I did not even notice that the table name in the code you provided was “Drama”. I’m very sorry for having wasted so much of your time when I ought to have seen the problem straight away - my tables is “Dramas”

Also, I’m humbled that you took the time to build the test table for the genres, thank you. My list of genres is currently up to 35, so I think it would be a bit unwieldy in terms of the number of permutations. East Asian Dramas have a number of very specific tropes that are treated as separate genres, and the longer Dramas often blend several of them. I think I’m likely to end up with 40 or so.

However, I’m interested to compare the 2 approaches to see which is easiest to work with, so I think I’ll try the method you have so patiently outlined in one version of my DB, while trying the “linking tables” approach in another.
As I had only just started adding genres, I ran 3 separate Select Distinct queries, and manually pasted the results into one calc file for later import. This means I am all set to try both methods - one with all 40 “genres” and one using your suggestion with perhaps 12 or so. Again I am indebted to you for your patient and generous assistance.