[dba-SQLServer] Opinions invited: question about normalization

Arthur Fuller fuller.artful at gmail.com
Sat Dec 27 12:13:54 CST 2014

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?


More information about the dba-SQLServer mailing list