Highly Normalized Data and Wizards

[Thales750]

I’ve been making my systems more and more normalized. My filter for something occurring more than once is infinity. So the question for normalizing is will it ever happen and can there ever be two.

I am now storing all companies (whether vendor, customer or any other) in one list.

In other words there is no longer a “tblVendors” or a “tblCustomers”, there are now Vendor Agreements and Customer Agreements that each reference the mother table “tblCompanies”.

Likewise all contacts in one list. And in both cases all of them have daughter tables for any data that can ever change,
So the table tblContacts can only have “FirstName”, “LastName”, “DateOfBirth”, and if needed SS#, that’s it. Everything else is in daughter tables.


There are a lot of problems with this.
1. Every time you are going to add a new customer or a new vendor you must look both the tblCompanies and the tblContacts to make sure those names don’t already exist and if they do, are they duplicates etc. etc.. The list goes on and on…
2. You must find a way to limit queries to one phone number, one email, one address, etc. etc.

I can’t tell you how much extra work this has caused and how many unforeseen data management issues; and users finding ways to do things you would have not dreamed possible.

So my new answer is wizards; for any data entry that involves new accounts or new contacts or something involved.

 

[Galaxiom]

My filter for something occurring more than once is infinity. So the question for normalizing is will it ever happen and can there ever be two.

You must find a way to limit queries to one phone number, one email, one address, etc. etc.

 

[dportas]

I’ve been making my systems more and more normalized.

You mean BCNF? 5NF? Something else?

There are a lot of problems with this
1. Every time you are going to add a new customer or a new vendor you must look both the tblCompanies and the tblContacts to make sure those names don’t already exist and if they do, are they duplicates etc. etc.. The list goes on and on…
2. You must find a way to limit queries to one phone number, one email, one address, etc. etc.

These sound like functional requirements rather than anything much to do with normalization. I.e.: If you require unique names then you require some way of eliminating duplicates from your system. If you need to store multiple phone numbers, emails, addresses, etc but you only want to return one of them then you ought have a way to specify which one you want. Talk to the product owner / end user about their requirements, create a wizard if it helps, but "highly normalized" data isn't any part of the problem it's just one part of an effective solution.

 

[The_Doc_Man]

To my way of thinking, and I admit that often I am more pragmatist than purist, it is sometimes possible to over-normalize a table. My view is that you normalize until you have at least a 3NF that still matches your data flow model. If you normalize in a way that your model no longer tracks reality because you normalized everything together in one table - but the model says that's wrong - then you did yourself no favors.

If your business model says that companies can be either sellers or buyers and you are just a broker, then a single table for companies is right. You would use a junction table to join two entries from the same table. But if your business model says that companies are in two distinct lists - sellers and buyers - then perhaps your table structure should match the problem even if you COULD normalize them. OK, a junction table and a single table that self-joins through the junction table would still work, but the question would be to determine whether you introduced extra complexity in that case by merging the two disjoint tables.

Since this is an abstract discussion, there is no right or wrong answer. In the case of specific problems, it might be arguable or blatantly obvious. But whatever it is, the problem is king and the details behind the scenes are less important than that problem.

Part of that is the pragmatist speaking; the guy who wants to do the least work that does the job right. Part of me is the purist speaking; the guy who wants to build a good correspondence between the Access business model and the real-world business model. It's all a matter of balance.

 

[Thales750]

Once again Doc, it is a real pleasure to be acquainted with someone of your insight.

I was not necessarily referring to formal normalization, as our formally taught brethren might have inferred; but more to more accurately reflect the real world.

It seems to me that the interconnections between objects in the real world are inextricable.

Bob had been in love with Judy for months, but she didn’t even know. Every week she would call him up and he would go to one of her listings and make a proposal for some repairs or improvements. Judy sold real-estate, Bob of course was a home improvement contractor. After months of referring work to Bob, she had him over to look at her patio.

Sure enough, Bob fixed her right up, so much so, she married him. Now Bob had always kept track of all of the contacts he got from her but now some of them were more than her contacts, they were theirs.

And the list goes on and on.

I cannot conceive of a real world system that would not use a single unique table to store contacts. The case for Vendor and Customer could be made, that if ever (infinity) a customer could become a vendor then it must follow that they should be in the same table, else normalization is broken.

When I was at the TSA (DHS), I was constructing a system to redact documents. That small department examined over a million docs per year and each one of them three times by a different analyst. I once asked a manager if an event could occur. His answer was never, so I clarified my definition of “never”. I said “never” is a large rock that will fall from space and remove the planet Earth from the list of life bearing worlds.

His answer was, “that’s right it will never happen”; of course it happened the very next day.

Now I could entertain you for hours about the arguments I used to have with him, but I’ll save it for the water cooler. In the end he was one of my greatest allies.