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

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.

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

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

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.