The queries handle the RI.
NO, they don't.
Millions of records later, no RI, no problems.
Have you actually looked?
I've spent a few years making all this work.
Not surprising when you won't use the tools provided by the database engine and are using a design that does not comply with standard design patterns.
1-m relationships between tables are referred to as parent-child because it is a concept we all understand. A parent may have many children but a child may have one and only one parent (in the concept of a 1-m relationship). You have a design where you are saying that one child record may have FIVE different parents. That is the problem. You needed to create the relationships between the entity (whatever that table name is) and the child records. All the tables that you think are parents, presumably have a 1-1 relationship with the actual parent which seems to be missing from your schema and that is probably why this design is so awkward to work with and requires union queries.
Not if every Insert in the entire database has data integrity and is inserted using a query. No data in these diagrams is able to be deleted at all or inserted directly. Database should never contain deletions. They should archive data, and every change of status should have a history.
I could delete data from the tables
You also have an unnecessary relationship which you have highlighted in yellow for some reason. Since you don't enforce RI, this could be completely invalid and you would never even know. There is almost certainly some bad data out there waiting to be found so you might want to revisit the "millions of records later" assertion.
I am just interested in how you have related tables
If you think I'm wrong and you can correctly enforce RI on this many-parents to one child, please take the time to build three tables. Two parents, one child. Put some data in the parents, make sure the set of PKs is not identical so put 3 records in one parent and 5 in the other. Then create the relationships. The schema seems to work until, you try to add a child record with a FK that does not exist in ALL parents. Since the 5 "parent" records hold completely different sets of data, the autonumbers will soon diverge and one table will contain IDs that don't exist in all the others.
This is why, when you create a schema like this, you create an Entity table which contains the type code plus ALL the common fields so you can reduce the times you have to include the 1-1 tables in the join. The 1-1 tables only contain the FK and the columns that are unique to that Entity type. Beyond that, ALL relationships go to the Entity table which serves as the parent. If you have related tables that belong to one and only one of the entity types, you could conceivably deviate and relate to a specific entity type table but I probably would not.
