Design for displaying data from many tables

@Tray2 The problem is I am not in charge of the DB and from some reason they decided to split each data table to separate table so I can't do these changes and I somehow have to "concentrate" all the rows from all these tables which were marked with the dropdown value

@Ligonsker Are you allowed to create database views?

@Tray2 I will ask tomorrow about views

Btw regarding the goal of it (Which I'm not fond of): It's sort of a "reminder" table to let users know what rows they marked in the other tables and the dropdown is the priority of the reminder - it's an optional dropdown, but - if it's marked, it means it goes to this "reminder" table where they see all the things they "kept for later" if that makes sense.

If I can use views (will know tomorrow) - what should I do?

And if not - what options do I have? I was thinking about using a transaction and update two tables together - the table where you edit the dropdown, and then update the second "reminder" table with the relevant data (table_name, foreign_row_id, identical_input1, identical_input2, new_dropdown_value) with a transaction so to keep the integrity. (I feel like having two sources of truth is a faulty design but I might have to? so at least keep the integrity with a transaction)

Or maybe something else?

@Ligonsker The views are basically a set of queries that you make into a read only table. You can read more about that here. https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

The reminder table sound a bit like a pivot table to me, and by using a polymorphic relationship you could join more than two tables together.

So if you have three columns in your reminder table like so

• table_name
• user_id
• reminder_id

The reminder_id would then be the foreign key for the table_name.

You can read more about that here

https://laravel.com/docs/9.x/eloquent-relationships#many-to-many-polymorphic-relations

If you need to update more than one table at the time, you don't really need to use transactions. There is no need to use transactions unless there is a need to roll back changes made in the first table if the change in the second table fails. For example money transactions between two accounts.

@Tray2 thanks, I will see if I can use polymorphic which I've never used. (Or views)

But, v you said join tables - in this case I don't need join, I need something else, it's like adding completely new row from the other table to the the new table

The row_foreign_id is not the user_id but rather the id from the table that was added to the new table ( so as you said it acts like many to many )

And also, if views are possible - what query should it be? Should I do a UNION ALL between all the tables and the new table every time one of the tables updates? Or something else?

One more thing - why not transactions? What if a use update a table successfully but then the update to the reminder table fails? He won't see it even though he selected it

@Ligonsker That depends on what your database model looks like, and from what I gather from you is that it looks like shit.

@Ligonsker I still suggest you take this training, it is free:

https://laracasts.com/series/laravel-8-from-scratch

And

https://laracasts.com/series/whats-new-in-laravel-9

Almost all of the questions you asks here are covered. At least get some basics. This is only a suggestion.

@Tray2 It does 😂🤣! Problem I came here not too long ago when everything was already built upon it and I don't see how it can be refactored in a short time while still working on the app.

The model is simple but bad in my opinion - they assigned a new DB table for every data table

Would solution #2 be the best in this case then? i.e to create a new table and just insert the table name that was updated, the row of that table and the identical columns? Trying to think about soemthing else but can't find anything that will be simple and performant as that

Also I will request to add that priority column to all the tables so that they also have that data for any future refactoring.

Also, in this way, what I can do is to then allow user to click on the row on that new table and fetch the entire row from the original table so that they see all the columns

But please enlighten me to new ideas, I really want to know if I have better options in this case as you said:

from what I gather from you is that it looks like shit.

@jlrdw I watched that but I don't think this specific case is covered - what solution would you choose in this case where you have hundreds of tables that you need to combine their mutual data into single table?

@Ligonsker I would work it out in stored procedures. Of course backup every thing first in case something goes wrong.

Oh, start with pencil and paper to write out exactly what you need, it helps with the coding.

@Ligonsker Can you share some migration examples?

@Tray2 There are no migrations :/, they create everything separately in MS SQL outside of Laravel

@jlrdw Already have the pencil and paper :P

You suggest that I still keep that new table, however instead of updating this reminder table from the code in Laravel, a stored procedure would do that?

Or you also mean not actual table but a stored procedure to a materialized view or view? (Although I think an actual table would be better?)