A Case for Ignoring Normalized Data

[mike60smart]

Well I have always been told by numerous experts that Referential integrity is essential.
If RI is not enforced then you are able to add Child records in a related table.

The link between tblContactProfile - PK -ctpContactID and tblLinkUniversalAddresses on luaLinkToAddressesID
is a good example.

 

[Thales750]

In every tblUniversalLink a second foreign key designates the Parent Table. That link exist for everyone of those relationships. It allows an unlimited number of connections between tables. This arrangement creates many-many even between multiple tables.

It requires a union query to display all of the Parent Tables in the same record set.

 

[Thales750]

Well I have always been told by numerous experts that Referential integrity is essential.
If RI is not enforced then you are able to add Child records in a related table.

The link between tblContactProfile - PK -ctpContactID and tblLinkUniversalAddresses on luaLinkToAddressesID
is a good example.

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.

 

[mike60smart]

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.

Can you upload a copy of the database with no confidential data?

 

[Thales750]

You cannot enforce RI in the second version, at least not once you have actual data in the tables. That is the problem with it. The universal contacts must link to the customer profile not the intermediate tables.

Not True, Well not necessary anyway. The queries handle the RI.

 

[Thales750]

Can you upload a copy of the database with no confidential data?

I'll have to make a new one that is not part of the overall program. between the front end and back end it is a couple of hundred megabytes. When I migrate this to SQL I'm going to break it down into components. At that point you can have a look. I've spent a few years making all this work.

 

[mike60smart]

Thanks, you can use the following website to transfer up to 2gb file

 

[Thales750]

Thanks, you can use the following website to transfer up to 2gb file

What would be the purpose of uploading?

 

[mike60smart]

What would be the purpose of uploading?

Hi
Sorry, if you decide to upload a sample of your database then there would not be a reason
for using the link.

 

[Thales750]

Hi
Sorry, if you decide to upload a sample of your database then there would not be a reason
for using the link.

What would be the purpose of sharing this database?

 

[mike60smart]

I am just interested in how you have related tables

 

[Thales750]

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 pattern

I could delete data from the tables

Of course you could, that's not the conditions though is it? Users can't, they're the only ones putting in data.

 

[Thales750]

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.

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.

 

[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.

 

[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

 

[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.

 

[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.