Jim Lawrence
jlawrenc1 at shaw.ca
Sun Jan 4 00:31:57 CST 2015
Hi Arthur: This reply may be a little late but as to your concerns I have done this type of indexing a few times. To start with all Ridings have a specific number and polls within the Riding are accompanied by a full legal description. A query can be written using the legal description. It can be an arduous process but once written (the 100 or so) it is probably good for many years. When complete all Riding and Poll values can be assigned. There are ways to optimize the search algorithms but once you write a few it will become apparent. I used a drop-through resolution method which can be even faster than a full query. For example; many roads are exclusive to one Poll and if not an address range is usually definitive. It is not a quick process all around but well worth it. Most importantly, you have to get many people doing data entry. Last time I managed a couple Ridings it took about thirty volunteers and a weeks worth or work. ;-) Jim ----- Original Message ----- From: "Arthur Fuller" gmail.com> To: "Discussion concerning MS SQL Server" databaseadvisors.com> Sent: Monday, December 29, 2014 9:04:28 AM Subject: Re: [dba-SQLServer] Opinions invited: question about normalization First of all, thanks for your inputs and suggestions. While awaiting same, I have plogged along on my own and devised a tentative scenario. First, some basics that describe the domain (and granted these may describe only the Canadian version of democracy in practice). 1. We have Electoral Districts, and they exist at two levels, called Federal and Provincial. These districts map in parallel 99% of the time. 2. Here in Canada, we have three significant political parties and a couple of others. 3. The individual candidates tend to work on their own, They rely on a team of Volunteers -- "fellow-travellers" willing to donate their time and effort toward the larger cause. 4. Candidates and/or staff relegate tasks to the available Volunteers, who may in turn be collected into Crews: a) Canvass (That means door-knocking); Sign Crews (that means delivering signs for either window or lawn or possibly both, and there's a further wrinkle, because there has recently emerged in which lawn-signs in particular have been damaged, defaced or outright destroyed, and we need to keep track of these incidences.) 5. Street Addresses are in themselves problematic. There's no simple division between odd or even. There's no simple way to deduce the address from a street number, but on the other hand, the list of addresses is delivered by the government, and issues of trust aside, the "facts of real estate" imply that since the last census was performed, several condos have been built and inhabited, and these inhabitants are not on our immediate Voters' List. So the app has to include a way to insert rows into our mobile database, and upon return to HQ, upload said inserts into our HQ database. 6. Alpha Anywhere solves all of these problems in a single stroke. That is why I have bet my future on this technology. I ought to state this more precisely: I have no financial interest in Alpha Software. I have never received a penny from Alpha. I have simply viewed what the software delivers, and in the course of numerous emails, become friends with Richard and Selwyn. I have never met either, but I have learned that two of my role models, Dan Bricklin and Adam Green, are working with the Alpha team, and this makes me immensely happy. and delegate tasks and responsibilities on an ad-hoc basis. On Mon, Dec 29, 2014 at 11:26 AM, Gustav Brock <gustav at cactus.dk> wrote: > Hi Arthur > > I think you are mixing two very different aspects here: User interface and > filtering method. > > Having multiple "and" or multiple "or" is not so difficult, but mixing > and/or is. I don't think there's an universally perfect method, it depends > on your application. > > As for your search techniques, maybe some of the new BI systems can be of > help if you can define the volunteers' properties as dimensions. > For a project, I found that copying the normalized data to (nearly) flat > search tables was quite effective while keeping the "true" data in the > original structure. This also allows for an easy (though not instantaneous) > complete rebuild of the search tables from scratch. It was done in SQL > Server, no fancy tools. > > /gustav > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto: > dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur Fuller > Sendt: 27. december 2014 19:14 > Til: Peter Brawley > Emne: [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 > > -- Arthur _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com