A Case for Ignoring Normalized Data

[Thales750]

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.

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.

Of all the things you said, this is closest to making sense. Yes, you could have a field for each parent entity table. That would actually work. It would be a little less flexible, it would still require a union query to retrieve the data backwards. But, it would work.

 

[Pat Hartman]

You must have missed part of the previous conversations. Each Relationship has a dual key. With a dual key each record relationship is unique. I'm not the only developer to use a second key to define an area. One of the few though.

That isn't visible in the images you posted and the only reason you need the dual key is because you omitted the Entity table which would have simplified the whole process and which is the way this design pattern is normally implemented so that you can enforce RI.

In any event, it doesn't matter. The only way RI works with your schema is if every parent table has every combination of the two field key.

I don't expect you to go back and change your whole schema just because I pointed out the flaw that child records can't have multiple parents. You've invested a lot of effort to make this work. Just don't be suggesting it as a good example because an example that prevents the use of RI is not a good model to follow.

 

[Thales750]

That isn't visible in the images you posted and the only reason you need the dual key is because you omitted the Entity table which would have simplified the whole process and which is the way this design pattern is normally implemented so that you can enforce RI.

In any event, it doesn't matter. The only way RI works with your schema is if every parent table has every combination of the two field key.

I don't expect you to go back and change your whole schema just because I pointed out the flaw that child records can't have multiple parents. You've invested a lot of effort to make this work. Just don't be suggesting it as a good example because an example that prevents the use of RI is not a good model to follow.

Each one does have 2 fields. the first diagram shows this and also has a note that tells you they don't all show. It would have had too many lines

 

[Pat Hartman]

Of all the things you said, this is closest to making sense. Yes, you could have a field for each parent entity table. That would actually work. It would be a little less flexible, it would still require a union query to retrieve the data backwards. But, it would work.

The Entity table contains the EntityID, type code (5 types you have), and however many common fields you have among all the tables. ALL tables relate to the Entity table and none (usually) to the 5 type tables.

Each one does have 2 fields. the first diagram shows this and also has a note that tells you they don't all show. It would have had too many lines

It doesn't matter. You still can't enforce RI. If you cannot enforce RI, there is something inherently wrong with the schema. I'm not trying to beat you up about this. What's done is done and you're never going to undo it but this is not a model I would show to experts.

 

[Thales750]

The Entity table contains the EntityID, type code (5 types you have), and however many common fields you have among all the tables. ALL tables relate to the Entity table and none (usually) to the 5 type tables.

It doesn't matter. You still can't enforce RI. If you cannot enforce RI, there is something inherently wrong with the schema. I'm not trying to beat you up about this. What's done is done and you're never going to undo it but this is not a model I would show to experts.

I don't care about RI. In cases where Universal Tables are used. you have to use a query to insert a record. It's sloppy to allow direct entry into a entity table no matter. So RI is unnecessary.

Four, four records in the tblLinkUniversalContacts table. Those were one hundred percent created during development testing.

 

[Pat Hartman]

I don't care about RI.

There is simply no response to that. I really hate seatbelts.

In cases where Universal Tables are used. you have to use a query to insert a record. It's sloppy to allow direct entry into a entity table no matter.

No, you don't. You simply have to understand how to use a left join. The related row gets added when you have data to add to it. If you want to force the related row to be added, base the form on a query that joins the two tables and add a line of code to populate the FK field. Then your validation code in the BeforeUpdate event can ensure that all required data is present or prevent the record from being saved.

So RI is unnecessary.

We're going to have to agree to disagree on that point.

 

[Thales750]

I am just interested in how you have related tables

I will get around to creating a little program for just Contact Management. The only part of this database that uses the Universal Links is the Contacts and the Notes. The Notes part hasn't been built yet. The use case is there, maybe.
We also use this for saving URLs this allows us to link to a website from any of the Database areas.