[dba-SQLServer] Opinions invited: question about normalization

IMHO, there are all sorts of rules and standards. When starting in the business of database understanding and following the tried and true recommendations is the only logical way to go but as you become more experienced you learn when to break the rules.

When it comes to extracting the appropriate data from a data store, as you become more experienced, rules become guidelines and not walls. Some senior database gurus use hybrids, of their own designs, to rip matching data, sql to further filter, sort and organize and then functions to assemble the final results. 

The only criteria then is accuracy and performance.

Jim     

----- Original Message -----
From: "Arthur Fuller" <fuller.artful at gmail.com>
To: "Peter Brawley" <peter.brawley at gmail.com>
Sent: Saturday, December 27, 2014 10:13:54 AM
Subject: [dba-SQLServer] Opinions invited: question about normalization

I have in development an app that is intended for use by agencies that use
a lot of volunteers. My initial design was fully normalized (by which I
mean I went to 5NF not just petty 3NF), but this posed significant problems
on the design of the FE, which is web-based. This means that attached to
the Volunteers table are several child tables, each of which connects to a
lookup table, and each of which child could have several rows. For example,
consider Languages-Spoken, easy enough in itself, point to a lookup table
listing languages, present a combo-box and that's that. But now we add
Skills (Driver, Cook, Visits, etc.), and now just with these two lists,
it's already complex: I need a Russian-speaking volunteer who can also
serve as a Driver or Cook, say.

That leads to complex queries that eventually involve nested Having
predicates, and that is tough to implement in a UI that recognizes that
most of its users are, not to cast aspersions, but let's face it, a lot of
users in this category are going to have problems with the intricacies of
ANDs and ORs.

So I was thinking that there's another design strategy in which all the
lookup tables are folded into a single table, with an identifier that
describes the domain (i.e. languages spoken, skills offered, fields of
interest, etc.), the point being that a successive number of predicates
could be assembled from a single table, thus avoiding the complexities of
multi-table queries.

I know that this design violates virtually everything Dr. Codd has written,
but he was after all a mere demi-god, and as problems have progressed and
products such as NoSQL have emerged, I've begun to doubt propositions I
formerly held absolute. Disk space is cheap, speed is abundant, and (well
not in my particular case) databases are expanding to billions of rows.

Any thoughts, my friends?

-- 
Arthur
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com