Jürgen Welz
jwelz at hotmail.com
Sat Feb 21 12:09:40 CST 2004
Start normalized and denormalize should performance issues arise. Querying bit flags can be very fast and efficient but there is a learning curve and it is quite steep if you want to return a recordset using a where condition of 'Answered yes to a certain set of questions, A to another and not D to yet another set ''OR'' another set of criteria'. If there are standard groups of questions that are asked over a period of time, you may want to use a junction table between a table of questions and a defined question block. If the questions in a ResponderSurvey are generated ad hoc it will be necessary to store the foreign key of each question and its answer in each survey record. I didn't catch the start of this thread but the topic is one of my favorites. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com >From: "DJK(John) Robinson" <djkr at msn.com> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: "'Access Developers discussion and problem >solving'"<accessd at databaseadvisors.com> >Subject: RE: [AccessD] Table Structure questions >Date: Sat, 21 Feb 2004 17:39:53 -0000 >MIME-Version: 1.0 >X-Originating-IP: [81.129.30.231] >X-Originating-Email: [djkr at msn.com] >X-Sender: djkr at msn.com >Received: from mc12-f1.hotmail.com ([65.54.167.137]) by mc12-s4.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Sat, 21 Feb 2004 09:42:13 -0800 >Received: from databaseadvisors.com ([209.135.140.44]) by >mc12-f1.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Sat, 21 Feb 2004 >09:41:27 -0800 >Received: from databaseadvisors.com (databaseadvisors.com >[209.135.140.44])by databaseadvisors.com (8.11.6/8.11.6) with ESMTP id >i1LHeBM32066;Sat, 21 Feb 2004 11:40:11 -0600 >Received: from hotmail.com (bay5-dav21.bay5.hotmail.com [65.54.172.125])by >databaseadvisors.com (8.11.6/8.11.6) with ESMTP id i1LHduM31764for ><accessd at databaseadvisors.com>; Sat, 21 Feb 2004 11:39:56 -0600 >Received: from mail pickup service by hotmail.com with Microsoft >SMTPSVC;Sat, 21 Feb 2004 09:39:56 -0800 >Received: from 81.129.30.231 by bay5-dav21.bay5.hotmail.com with DAV;Sat, >21 Feb 2004 17:39:56 +0000 >X-Message-Info: vGzX0e+ktu7ZFEgZNgxxTXZAKJwb4Y3e/D28QRQNxYg= >Message-ID: <003f01c3f8a1$b6fb9530$bf00a8c0 at dabsight> >X-MSMail-Priority: Normal >X-Mailer: Microsoft Outlook, Build 10.0.4510 >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 >In-Reply-To: <4037BFD9.25690.26D8437 at localhost> >X-OriginalArrivalTime: 21 Feb 2004 17:39:56.0131 >(UTC)FILETIME=[B842BB30:01C3F8A1] >X-MIME-Autoconverted: from quoted-printable to 8bit by databaseadvisors.com >idi1LHduM31764 >X-BeenThere: accessd at databaseadvisors.com >X-Mailman-Version: 2.1.4 >Precedence: list >List-Id: Access Developers discussion and problem >solving<accessd.databaseadvisors.com> >List-Help: <mailto:accessd-request at databaseadvisors.com?subject=help> >List-Post: <mailto:accessd at databaseadvisors.com> >List-Subscribe: ><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=subscribe> >List-Archive: <http://databaseadvisors.com/pipermail/accessd> >List-Unsubscribe: ><http://databaseadvisors.com/mailman/listinfo/accessd>,<mailto:accessd-request at databaseadvisors.com?subject=unsubscribe> >Errors-To: accessd-bounces at databaseadvisors.com >Return-Path: accessd-bounces at databaseadvisors.com > >I think you've all lost the plot, mostly: > >1. Each individual answer *has* to identify the question it relates to, >if you want to make any sense of the analysis! (Re-read Mark's posting, >repeated below. There are different simultaneously existing sets of 25 >questions, and each set varies with time.) > >2. So a million records at 6 bytes each gives you ... 6 MB! Maybe I've >been working too much with SQLS lately, but this minuscule amount, coupled >with the simplicity and flexibility of design, and ease of >querying/reporting, is "more efficient" (Mark's phrase) than groping about >in strings. >IF (really?) you think that disk space is the only criterion, then you >shouldn't be wasting a whole byte on each answer, when a couple of bits >would do! > >Stick to Normal form, unless there's a GOOD reason not to. > >John > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > > Stuart McLachlan > > Sent: 21 February 2004 10:30 > > To: Access Developers discussion and problemsolving > > Subject: RE: [AccessD] Table Structure questions > > > > > > On 20 Feb 2004 at 18:25, DWUTKA at marlow.com wrote: > > > > > Sorry, use 97 for almost all of my backends....so I keep forgetting > > > about the Unicode issue! <grin> > > > > > > However, what are you LINKING the records in that table with? Each > > > record, in a one per question table, would need a caller ID (Long), > > > Question Number (could be byte), then the answer (another byte). > > > > > > Now you have 6 bytes per answer (instead of 1 in ASCII, or 2 in > > > Unicode!) > > > > > > > No, I agreed completely with your idea of storing all the answers in > > one record, I just suggested stotring the anwers in separate fields > > rather than a string - so you have CallerID plus 25 byte fields > > rather than CallerID plus one string field storing 25 characters. > > > > >****************************************** >Mark's original posting: >****************************************** >Hello All, > >I have been asked to set up a call monitoring db for several different >groups. A few pieces of data will be captured for each call and then 25 >questions will be answered for each call. Each group may or may not have >the same 25 questions...which they can add or remove throughout the month. >Initially I wanted 2 main tables for data collection(1 for call info and 1 >for Answers)...The Answers table would have 1 row per question(instead of a >column for each)...so 25 records per call monitored. I thought this would >be best so as new questions were added and discontinued...there would be no >need to change the forms,tables, or reports...but with this method I will >be > >over 1 million records in 6 months. I've informed them they will need some >BE other than access for this scenario. > >The question I have...is there a more efficient way to store this type of >data?(Also, the answers are all 1,2,or 0...so its very little info...just a >lot of rows) > >Thanks, > >Mark >****************************************** >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca