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? -- Arthur