Over-normalizing Data - means extra DB hits. Issue?

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Over-normalizing Data - means extra DB hits. Issue?

Post by Jaymer »

MSSQL used


I converted a system for a customer.
They had lots of user configurable tables...
Customer Type
Shipping Type
Ship Via
EquipCondition
etc etc

So when the system converted over, I had a Customer Table with a CustType reference field.
I'm slowly denormalizing these as I come across them. Why?

In this specific case, the linked table only held the expanded text for Customer Type:
Prospect
Lead
Active
Dead

Aware makes it easy to have referenced tables, and it also makes it just as easy for Customer Type to be PLAIN TEXT (varchar length 15 or 20, for ex) and to still use the CustType lookup table as Dynamic Choices for a combo box. Store the entire CustType text in the Customer record.

Using a reference field uses 3 fields:
(10 bytes for REN... 2 bytes varchar, 8 bytes for 'CUSTTYPE')
(8 bytes for RID bigint)
(2 bytes for RMA... 2 bytes varchar, then its usually null, esp. in this simple reference case)

So I have a minimum 20 bytes used to make a pointer to the lookup table, AND Aware is doing an extra db call every record to resolve the shortcut.

So NOT USING A REFERENCE would seem to take less space AND require less resources.

RUNTIME ISSUES:
A Plain Text with Choices COMBO BOX functions differently than a Reference COMBO BOX w.r.t. typing into the field when parking
No "+" Add Option is available - since aware doesn't hasn't been programmed to realize these are Dynamic from a table that COULD be added to


However, even if it didn't take less space IN THIS CASE, I'd do this option to save a db hit on every customer grid or customer form that ever comes up... and I'll keep trying to eliminate more unnecessary db hits.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Over-normalizing Data - means extra DB hits. Issue?

Post by BLOMASKY »

A few comments.

1) with a small lookup table, it is all cached so the actual DB hits are small
2) I do agree, that if you normalize the heck out of everything, you won't get any work done, so I always think, "is it possible the end user will want to change the description and/or add - remove options. I.e. change "Dead" to "on life support"
3) Space should not be an issue. Disk storage is cheap. Gone are the days where I worried about saving a few bytes per table.

Bruce
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Over-normalizing Data - means extra DB hits. Issue?

Post by johntalbott »

Jaymer - Are you running into performance issues? It would be a surprise if the bottleneck is related to database table joins.
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Over-normalizing Data - means extra DB hits. Issue?

Post by Jaymer »

johntalbott wrote:Jaymer - Are you running into performance issues? It would be a surprise if the bottleneck is related to database table joins.
Inside the new logger, you'll see there are NOT database joins for shortcuts.
If you have a reference to a Customer table, with 5 reference Shortcuts, then I think the logger shows 5 separate db calls to resolve each Shortcut, even if all shortcuts are from the same referenced table - but I can't check my production log right now cause of a bug that keeps Production Log from being enabled sometimes, even though its selected for logging. And I can't restart Aware right now to correct it. But this type of reference isnt the object of this thread.
A simple ps_DeliveryMethod to a DeliveryMethod lookup table would also make you have a sc_DeliveryMethod, and thus the extra hits to resolve each Shortcut.

No reference = no extra lookups because you don't need the shortcut because you de-normalized to put the lookup value in the main table via Dynamic Choices combo box.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Post Reply