Need advice on database

[Mr.Rubi]

Hello,

I’m new to creating and using databases, so my problem may not seem serious to you. But I’m really stuck.

I have two tables. The data are as follows:

I understand that this relationship is contrary to the relational model, and it should be done through the third table. But I do not understand how to implement it correctly and use it to perform the necessary tasks:

1. Each column is unique but filled with non-unique data.
2. There are checks between the columns. So if column 1 is not filled at record, in this record it is impossible to fill column 2, etc.
3. Each cell of the record in table 1 is colored in the color that is taken from the record in table 2.

I will be grateful for advice and recommendations.

Thanks in advance!

[ben]

I find it a bit hard to understand what you are trying to achieve.

Please give us an example of the data that you are putting in each table.

[psweb]

I think even though this may not be exactly the concept you are talking about, this might help you to understand how it should work.

How to Use MySQL Foreign Keys for Quicker Database Development — SitePoint

Once you have that mastered then look at

Get Inserted Record ID

There are some cases where you need to get the inserted record ID, for example - use it in another insert step, filter a query, update some data in another table etc. In our example we will show you how to use the inserted record ID in another insert step, which inserts data in another database table. The logic for accessing the inserted record ID is always the same - it doesn’t matter if you need it for filtering a query, updating record, sending an email or any other server action step. We h…

[Mr.Rubi]

I will give an analogy on films and actors.

There is a table of movies that has six fields for actors (lead actor, supporting actor, etc.). In this case, we will always have no more than six types of roles. Therefore, the number of fields in the table does not change.

There is a table of actors, which contains the name of the actor and the color of the cell, for faster reading of the table.

It looks like this:

I understand that according to the relational model I have to implement these relationships through the third table to get like this:

My main problem at the moment is I don’t understand how to make this happen:

[Mr.Rubi]

I understand correctly, in order to build the above table correctly, I have to specify a logical expression for each column of the role (built with the help of a data formator), which would correctly filter the data and return what should be a particular record in a particular column of the role?

With the assignment of a particular record cell of the appropriate color, as I understand, is also done by constructing a Boolean expression (using a data formator)?

If so, I ask for help in constructing these expressions.

[Mr.Rubi]

After a more detailed study and many attempts, I managed to get off the ground. I realized that to implement this kind of table as in my example above, you need a custom query. At the moment, I managed to implement the table in the desired form (left table with a normal query, right table with a custom query): http://howitisdone.info/test_table.php

However, the color values are not passed correctly and I am unable to assign individually to each cell.

Here’s the query I’m using:

SELECT test_films.id_films, test_films.name_films, 
SUM(IF(test_typerole.type_role = '1', test_actor.id_actor, 0)) as Type_role1,
SUM(IF(test_typerole.type_role = '2', test_actor.id_actor, 0)) as Type_role2,
SUM(IF(test_typerole.type_role = '3', test_actor.id_actor, 0)) as Type_role3,
SUM(IF(test_typerole.type_role = '4', test_actor.id_actor, 0)) as Type_role4,
SUM(IF(test_typerole.type_role = '5', test_actor.id_actor, 0)) as Type_role5,
SUM(IF(test_typerole.type_role = '6', test_actor.id_actor, 0)) as Type_role6,
test_actor.color
FROM test_films
LEFT JOIN test_typerole ON (test_typerole.films_id = test_films.id_films) LEFT JOIN test_actor ON (test_actor.id_actor = test_typerole.actor_id)
GROUP BY test_films.id_films

I will be grateful for any help!

PS @Hyperbytes, you have a lot of experience with databases and queries to them, perhaps you can tell me what I’m missing and how to implement the task correctly? Thanks in advance!

[Hyperbytes]

Are the colours dependent on the count of that role for example 1 = Blue, 2= Green etc?

[Mr.Rubi]

Hello,

The color is contained in the actors table. Here is a screenshot of the tables that are used:

In the converted table, the numbers in the cells are the actor id:

And each cell should be painted in the color that is assigned to the actor.

[Hyperbytes]

Yes, this is going to be a bit or a nightmare. basically your current query only returns one colour per row (as you see) where you actually need one color value per role per row so you are going to have to effectively compute this in pairs

at present your query will return rows something like:

1,2,3,4,3,2,1,#FF633B

what you need is something like this

{{1,"#FF633B",2,"#AA633B",3,"#CC633B",1,"#FF633B",2,"#AA633B",2,"#AA633B"}}

i.e. 12 values, actor and color

Even then, it is going to be messy to implement the colour changes within the table

I am tempted to suggest returning value pairs i.e. 6 columns with a pair of values in them and use split() within app connect to separate the actor id from the assigned color code

{{“1,#FF633B”,“2,#AA633B”,3,#CC633B,“1,#FF633B”,“2,#AA633B”,“2,#AA633B”}}

Needed to think about this a bit but dinner is calling me

[Mr.Rubi]

I’ll think about it, too.

Bon appetit!)

[scott]

@Mr.Rubi,

I am just curious, have you thought about utilizing the Class toggle described here, Set Table Row Background Color with Dynamic Value for the color selections in your table?

As far as the database creation goes, it looks like you might have solved that. In the future, I would recommend using mysql workbench, it contains a modal option that allows you to create databases and their relationships visually. I have found it to be very easy to do and saves me lots of time.

[Mr.Rubi]

Hello, Scott

Thanks for the advice on the database design. I be sure to try workbench at work.

As for color. There is no other option but to dynamically add a class or style that will color the elements dynamically. So Yes, I use dynamic styling to the cell to color it in the right color. It is the style, not the class, due to the fact that the color changes dynamically and the class is not suitable here.

But the problem discussed here is not that. Two problems are discussed here:

1. How to extract data from the database, so that they built a table of the correct form (this task seems to have coped).

2. How to extract data from the database, so that the colors are correctly correlated with those cells in which these colors are stored (this has not yet managed).

[Hyperbytes]

Not forgotten you, just a bit manic at the moment, will get back to you when i can, basically ned to work out how to add a second column i.e. class1, class2 etc with something like

SUM(IF(test_typerole.type_role = ‘1’, test_actor.id_actor, 0)) as Type_role1, IF(test_typerole.type_role = ‘1’, test_actor.color_cell, ‘’) as color1 etc

but need to set up some data to check modelling