MySQL: multiple tables in relationship to one table: many-to-one?

I am looking for the ideas and advice in regards to my project. I had a good read about the table relationships such as: one-to-one, one-to-many and many-to-many. None of them suits my needs as it seems I am looking for something more like many-to-one, unless I just lack some understanding of those concepts.

I have a need to create 4 separate tables in my database and display data from all 4 tables on the same page. Then apply filters to it of such nature that one can display only T1 table data, T2 table data... and so on. Moreover, you should be able to filter provided data even further. To clarify my question please see the below example:

T1.events

• id int auto_increment
• listing_id int
• type varchar
• other fields

T2.deals

• id int auto_increment
• listing_id int
• type varchar
• other fields

T3.contracts

• id int auto_increment
• listing_id int
• type varchar
• other fields

T4.customers

• id int auto_increment
• listing_id int
• type varchar
• other fields

I need to make it four separate tables as the amount of data that they will hold might be enormous and therefore should not be mixed up into one table. On the other hand I need to display all of it on one page with possibility to apply filters. So I expect to have four 'tick boxes': events, deals, contracts, customers. If one is ticked we display just data from that table, if two are ticked we display data from both tables and so on. Moreover, once you tick let's say 'events' it should expand and show you further possible filters that can be applied specifically to the data taken from the 'events' table.

My idea was to create fifth table:

T5.common_table

• listing_id int auto_increment
• id int
• type varchar

So hypothetically if I would need to insert data into 'events' table the process would go as follows:

1. Insert data into 'events' table except of the 'listing_id'.
2. Insert generated 'id' and 'type' from 'events' table into 'common_table' -> that would auto_increment new 'listing_id' in 'common_table'.
3. Insert newly generated 'listing_id' into 'events' table. (the step which we skipped in 1.)

The rest of the tables: T1, T2, T3, T4 would have exactly the same relationship with my common_table.

From my understanding, I would have to follow below procedures if I would like to:

1. Display all data -> get data from common_table -> get data from each of the tables (T1, T2, T3, T4) appropriately with the 'listing_ids' gathered from the common_table.

2. Display one table data -> get data from common_table based on filters: type = events -> get data from T1.events based on the listing_id gathered from the common_table.

3. Display two or more tables data -> get data from common_table based on filters: type = events & type = customers -> get data from T1.events and T4.customers based on listing_id gathered from the common_table.

Implications to that idea:

• Whenever I would like to insert or delete entries in those tables I would have to perform multiple operations on the database as I would have to reference the table I am operating on (example: Events) and the table that stores all possible listings (common_table).

• I am not sure if that kind of solution is safe in terms of maintenance of the database, having four extremely huge tables that are interconnected by the common_table, is that a good approach?

• The common_table will consist of maybe three columns but it will grow huge as it will store reference to all four tables (T1, T2, T3, T4)

• When I want to display stuff or apply filters, again I have to operate on minimum two to maximum five tables

The main reason I need to build a relationship between the four tables are filters. When I display the data on the page I need to place it in 'one' list even though the resource of data is four separate tables. At first I want it to be displayed by date of the creation, regardless from which table I took the data from, but I want to be able to filter that data in several ways, so I must have possibility to reference the items on each of the four tables. This is where the common_table steps in, as instead of referencing each of the four tables I would reference only common_table and then based on its data escalate it to further four tables. In my head it would make my life easier, as when I want to display stuff I reference common_table, when I want to filter stuff I reference the common_table as well. It would be a bridge between four tables and operations on them.

I hope my explanation is clear, I code the project using Laravel + Vue but I do not think that information would be relevant, as the question is of a bit different nature. I would appreciate any feedback, recommended ways to deal with the issue or maybe already approved ways of doing it.

via Chebli Mohamed