Hi Crystal,
I concede on the documentation point. I do good document, but generally at the end of the project.
The project is in a state where I can easily transform the each table to a single variable whose content is the survey data elements comma separated. This would take my tables down by about 40, and add 40 rows/fields to a statistical table.
In doing so I am looking for pro's and cons.
As I see the pros and cons...it looks like this:
1. Is there a significant disk space savings, NO one has answered this yet.
2. Is there a significant reduction of access time in record retrieval, No one has answered that yet.
3. Does it make things simpler, probably not. I say this because the logical structure of Patient_Info to Office Visit to Statistical_Info still relates the same , just at a different level. It takes the relational complexity from access relational database management and places slightly more emphasis on internal program logic.
4. How about the consideration that out of the current 40 possible tests, (which creates 40 possible table entries), only half will probably be created during an office visit. As I understand it, those table entries will not be created using the current design, but using a Variable for each test would cause that variable (and its space) to be created and consumed for every visit. Is this a correct assumption/Theory ?
5. Now for usability and portability for my statisticians and clinical researchers. When a Researcher wants to perform analysis on a a single test result, say the Berg_Balance_Scale, I would have to develop technique/Macro/Code, to extract only that row for each office visit stored and create a specific file for the researcher which just contain the results of that particular survey. Not to mention logic to parse the comma separated variable so that it's usable from their perspective.
6. Anonymity ?...well I can go either way with that. But it is clear that the researchers prefer being "blind" to the Patient information.
7. Adding more survey latter ? With the current design, none of the other record need be altered. I simply add a new table and form, and move forward.
Crystal, I hope I don't come across abstinent or belligerent, I simply challenge myself, and others in an attempt to make GOOD decisions and provide a good product which meets the needs of my audience.
Just because this schema may seem "different", or "Unorthodox", does not mean that it wont work. That being said, I can accept someone saying "This design is BAD!", but I want to know WHY it is bad? Does it take up to much space ? Will it take 1 minute to get access to a record ? Will Access smoke and burn when it reaches 100 Patients ?
Or do we say its bad just because it does not fit the mold of conventional wisdom ?
Please please, be as frank and as critical as you wish. It only makes me a better developer.
Yours Terence
PS I can indeed convert this design relatively easy, and am leaning that way, I just need to convince myself, and I am not there yet :)
--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi Terence,
>
> you're welcome :)
>
> > "Will the Large number of table come back to haunt me ?"
>
> come back? ... they are haunting you now. Much of what you put in fields belongs in records. That is why I suggested you study Duane's survey database. What is a survey? A bunch of questions that can have answers with different data types, ... with easy reporting/slicing and dicing. Each of your (non-essential) tables, such as "3_meter_walk_test_fast" is like a survey with a particular set of questions.
>
> A relationship diagram should clearly show the main objects of your database. What is the purpose for all the tables? Yes, tracking results ... but does the subject not matter at all? An organized relationship diagram tells the story of your database. Don't skip the visual stuff. Organize the diagram as you build your tables ... relationships and table structure go hand-in-hand. In my opinion, a relationship diagram that is not organized represents a database with the same problem.
>
> It is never fun to hear that you need to go back to the drawing board ... better now than down the road before you invest more time into something without a strong foundation. Happy to say, however, that we can help you :) ... and whatever Duane says is gold -- I skimmed the doc he posted with the survey database.
>
> The sooner you set up the proper table structures and convert your data, the sooner your life can be better :) It needs to be done -- do it now.
>
> *** Steps for Documenting a database ***
>
> Make a PowerPoint presentation (or Landscape Word doc with tight margins) and choose blank slide (no placeholders)
>
> Even if you have never used PowerPoint, it is not hard. A presentation is a collection of slides. I like to use PowerPoint because the default is landscape with 1/2 inch margins, which fits a screen nicely.
>
> To create a new presentation, press CTRL-N (New). To create a new slide, press CTRL-M (Mew slide meow!). To save, press CTRL-S and pay attention to where it goes!
>
> you can delete the placeholders -- or change slide layout to Blank.
>
> switch between application you are documenting and PowerPoint (ppt)
>
> to copy screen to Clipboard, press the
> PrintScreen
> key on your keyboard
>
> to copy just the current dalog box to the clipboard
> Alt-PrintScreen
>
> to paste contents of Clipboard, press CTRL-V
>
> put screen dumps on seperate slides
>
> Document ScreenDumps of:
>
> : design view of forms
> (my hidden controls are always colored dark)
>
> : form view of forms
>
> : design view of reports
>
> : report view of reports
>
> : design view of each query and paste SQL in textbox on the slide (add line breaks in SQL so that each RESERVED word starts a new line. Format text as ARIAL 8 point. Becoming familiar with SQL (Standard Query Language) is very valuable.
>
> : output of each query (just one screen is enough)
>
> : relationships with tables expanded so you can see all the fields (include all tables, even those without links)
>
> Save and Print ppt presentation
>
> Print all code -- I like to copy it into Word and format it for easier readability, and build a Table of Contents (Insert, Index and Tables...) using the Heading style tags (Heading1 ALT-CTRL-1 for the name of the module, Heading2 ALT-CTRL-2 for the sub/function declaration, Heading3 alt-ctrl-3 for whatever). FYI, all paragraphs are called "Normal" unless you change their style name -- a GREAT feature of Word that not too many people know about.
>
> get a marker and label all controls with the Name property so when you read through the code, you can see what it is referring to. It is also helpful to label the ControlSource if it is different -- I like to Name my controls the same as the ControlSource to avoid confusion.
>
> Get a lined piece of paper for each form and write each form name at the top and underline
>
> click on each control in designview of form, whenever there is an event, note it on your paper along with the sub/function that it runs.
>
> ie:
> form BeforeUpdate = UpdateMe()
>
> It will take you a few hours to make a good reference like this and then a few hours to understand what is happening, but you'll save lots of time for yourself because you will take a whole different approach to setting up databases.
>
> It is not necessary that you understand all the code in general modules -- just what to use when you need it.
>
> btw, if you want me to email you a manual on learning powerpoint (PDF format), send me your email address and make sure you can receive 5 MB attachment.
>
> run my free database analyzer on any database you want to understand the structure of more quickly:
>
> My Analyzer
> ... the ultimate Data Dictionary Tool
> by Crystal
> http://www.AccessMVP.com/strive4peace/Dictionary
>
> ... the more data in the tables, the more the analyzer can tell you. Initially, give most attention to the Table List and Deep analysis reports.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> > "Referential Integrity, I am on the fence with it."
>
> at least put it on to document the relationships ... then remove it selectively
>
> > "I was not very concerned about the visual sizing because I provided the BE."
>
> hopefully you see the error in that way of thinking ...
>
> > "I will indeed go back and read the relationships chapter"
>
> thank you :) ... takes more than one re-read ... you still don't have it. Read it till you know it like your own thoughts. Watch my "Learn Access" videos on YouTube too (the only ones posted so far are on planning, which is what you need to think about):
>
> http://www.youtube.com/user/LearnAccessByCrystal
>
> Put thought into your structures. The biggest advantage you have is that you have collected data and can re-engineer a better solution than you could envision when the database started.
>
> > "It is more designed as a document management system"
>
> the purpose does not matter ... change your perspective ... see your data generally, not specifically ... what else is like it? ... visualizing is important.
>
> > "standalone requirement "
>
> ok ... that does not get lost with normalizing data
>
> > "Naturally my concerns are Data Integrity"
>
> enforce RI. Remove RI on a TEMPORARY basis when data is to be imported.
>
> > "If we need to move to SQL SERVER"
>
> no way that should be considered until the data structure and relationships are solid
>
> > "Its certainly early enough in development that I could make that change."
>
> seriously, Terence, you need to quit thinking about changing what you already have ... your application needs to be started over. The sooner you accept that, the better you will spend your time.
>
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
> --- On Sat, 8/27/11, Terence wrote:
>
> > Thanx Crystal,
> >
> > Re: Referential Integrity, I am on the fence with it. The
> > reason to NOT institute referential integrity is because
> > there are circumstances where the Patient/Client may be
> > deleted, or the Visit deleted, from the Database, But Under
> > NO circumstances should the test results ever get deleted.
> >
> > The test result live on with anonymity.
> >
> >
> > Regarding The naming convention of ID, you are correct! It
> > is confusing, though I usually used fully qualified names
> > when referencing. I think I will take your advice on this
> > one and alter the ID names.
> >
> > Regarding the diagram - I have moved it into folder
> > Buffalome , named quickpic.docx. I was not very concerned
> > about the visual sizing because I provided the BE.
> >
> > additionally, due to large numbers of tables and number of
> > fields in each table, it is not possible to get a "Snapshot"
> > perspective of the entire schema on paper, nor screen. I
> > will indeed go back and read the relationships chapter.
> >
> > There is minimal logic within this system. It is more
> > designed as a document management system, with an emphasis
> > on keeping the individual test results in a standalone
> > fashion. The standalone requirement is because I need to be
> > able to provide the statistical results from test and
> > greatly preserve anonymity from patients. Additionally most
> > of our statistics people don't give a "Rats A**" about where
> > the data came from, they just want access to their table so
> > they can import it into either Excel or a SAS compatible
> > format.
> >
> > The requirement of adding these electronic collection forms
> > was an after though, thrown at me. Additionally at the time,
> > the number of electronic collection forms was about a half
> > dozen.
> >
> > Naturally my concerns are Data Integrity, Portability of
> > the individual tables and ex potability, Anonymity from the
> > parent record(IE Office_visit) which is called STAT in the
> > DB.
> >
> > Ia m then concerned about data access speed, don't want a
> > dog of an application. Disk space, I really don't care, we
> > got lots of it and can get more, so long as we don't exceed
> > any MS ACCESS limitations.
> >
> > If we need to move to SQL SERVER, that wont be an issue
> > either, exception being what DB adjustment are needed.
> >
> > John expressed, or his position was/is, as follows.
> >
> > Make each test form a single field within the Office_Visit
> > Table (i.e. STAT Table. Perhaps do something like comma
> > separate the individual fields of the Electronic Collection
> > form and store the entire result of the form as a single
> > Field.
> >
> > Its certainly early enough in development that I could make
> > that change.
> >
> > I just cant get a feel for which way to go, which will
> > work, which is more efficient and effective.
> >
> > Will the Large number of table come back to haunt me ?
> >
> > terence
> >
> >
> >
> > --- Crystal <strive4peace> wrote:
> > >
> > > hi Terence,
> > >
> > > I got a database where the relationship diagram is a
> > mess ... and I cannot find the relationship doc you posted
> > ... please let me see how you lay out the relationships.
> > >
> > > If all the equal-sized boxes IS how you lay out the
> > diagram, then you need to READ the relationships chapter in
> > Access Basics ;)
> > >
> > > Unless you have a good reason not to -- enforce
> > Referencial Integrity (RI) on all relationships.
> > Right-click on a join line to edit properties.
> > >
> > > Change fields named ID to something descriptive yet
> > still concise (StatID, TrialID, etc) ... each field name
> > (and this is my thinking) should represent one piece of
> > information in a database. If a field is used in more
> > than one table, it will have the same name (if there is only
> > one in child table). You have a field called ID in
> > several tables -- but they do not represent the same piece
> > of information. This is confusing and may lead to many
> > more errors.
> > >
> > > I may be busy for awhile -- so don't pull your diagram
> > down till I have it ;)
> > >
> > > You should study Duane's survey app. You may not
> > see the connection to what you are tracking -- but there is
> > the road to your best solution ...
> > >
> > > At Your Survey
> > > by Duane Hookom on roger's Access Library
> > > http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html
> > >
> > > Once you redesign your database, we can tell you how
> > to convert the data
> > >
> > > Warm Regards,
> > > Crystal
> > >
> > > *
> > > (: have an awesome day :)
> > > *
> > >
> > >
> > > --- On Sat, 8/27/11, Terence wrote:
> > >
> > > > Crystal , I just uploaded the BE to
> > > > folder Buffalome
> > > > Terence
> > > >
> > > >
> > > >
> > > > http://f1.grp.yahoofs.com/v1/YPhYTuMJhlq33vqsZcGnl0osi7yDNiZwv-zvlNeWBbvczFQhdI4cOgQR-x4GJYuGOSwXn_BI1pMNP9GilvR1pbNl5LQL3HUTmUWi6w/Buffalome/Mobility3_be.zip
> > > >
> > > >
> > > >
> > > > --- buffalome wrote:
> > > > >
> > > > > Crystal, how about I upload a zipped copy of
> > the back
> > > > end ? It is not populated except with a few
> > fictitious test
> > > > patients.
> > > > >
> > > > > Regarding normalization suggestions, I will
> > be looking
> > > > at that today. However, Normalization with
> > respect to
> > > > redundant data, there are only a few instances,
> > perhaps 5,
> > > > that I can identify redundant data situations.
> > > > >
> > > > > Terence
> > > > >
> > > > >
> > > > >
> > > > > --- "Terence" wrote:
> > > > > >
> > > > > > Crystal , I uploaded a quick
> > Diagram/schematic of
> > > > the relational tables. I think its simply called
> > Doc2...
> > > > word document
> > > > > >
> > > > > > I will upload a more detailed one
> > shortly. This
> > > > diagram simply demonstrates the number of tables
> > and
> > > > relationships. Most of the fields in the various
> > tables are
> > > > numeric, Yes/No, with (unfortunately) an
> > attachment field
> > > > here and there. I wanted to give you a flavor for
> > it in the
> > > > immediate.
> > > > > > terence
> > > > > >
> > > > > > --- Crystal <strive4peace>
> > wrote:
> > > > > > >
> > > > > > > Hi Terence,
> > > > > > >
> > > > > > > make a PowerPoint presentation (or
> > Word doc)
> > > > with screen shots of your relationship diagram
> > laid out
> > > > according to the guidelines in the Relationships
> > chapter of
> > > > Access Basics and post them. I would like to
> > see your
> > > > structure.
> > > > > > >
> > > > > > > Access Basics by Crystal (Bill
> > Mosca's
> > > > site)
> > > > > > > http://thatlldoit.com
> > > > > > >
> > > > > > > Warm Regards,
> > > > > > > Crystal
> > > > > > >
> > > > > > > *
> > > > > > > (: have an awesome day :)
> > > > > > > *
> > > > > > >
> > > > > > >
> > > > > > > --- On Sat, 8/27/11, Terence
> > wrote:
> > > > > > >
> > > > > > >
> > > > > > > > I have been working on a DB
> > which
> > > > > > > > started out small and grew
> > LOL. It is a
> > > > medical/statistical
> > > > > > > > DB, and the General format
> > was an
> > > > Information Table
> > > > > > > > (Patients name, address,
> > etc), An
> > > > Office_Visit Table,
> > > > > > > > consisting of measurements
> > taken during
> > > > that visit. Very
> > > > > > > > Straight forward.
> > > > > > > >
> > > > > > > > Then it turned out that the
> > > > measurements taken during each
> > > > > > > > office visit were derived
> > from tests
> > > > which required either
> > > > > > > > specific calculations or
> > questioners.
> > > > So what I did was to
> > > > > > > > create a popup form for each
> > test or
> > > > questioners. These
> > > > > > > > detailed elements of
> > information were
> > > > then stored in a
> > > > > > > > separate table linked to the
> > office
> > > > visit.
> > > > > > > >
> > > > > > > > Well I now have over 40
> > potential
> > > > tables per office visit
> > > > > > > > and counting.
> > > > > > > >
> > > > > > > > In discussions with John V.
> > (Which you
> > > > can follow on our
> > > > > > > > forum). John suggested that I
> > consider
> > > > making each test a
> > > > > > > > ROW of the Visit table, thus
> > reducing
> > > > the number of table
> > > > > > > > significantly.
> > > > > > > >
> > > > > > > > Now be aware, these probably
> > only 50 %
> > > > of these test are
> > > > > > > > actually performed during a
> > visit.
> > > > > > > >
> > > > > > > > John seemed to be
> > uncomfortable with
> > > > the large number of
> > > > > > > > tables, and seemed especially
> > concerned
> > > > about DB size.
> > > > > > > >
> > > > > > > > I am looking for comments
> > suggestions,
> > > > and any case
> > > > > > > > experience from other member
> > with a
> > > > similar situation. I
> > > > > > > > wrestle with LOTS of tables,
> > BIG DEAL,
> > > > "thats what a DB is
> > > > > > > > for". Or should I take a
> > more
> > > > conservative approach and make
> > > > > > > > the various test data as rows
> > to the
> > > > Visit ?
> > > > > > > >
> > > > > > > > Pit Falls ? Pros Cons ?
> > > > > > > > terence
> > > > > > > > TY in advance.
> > > > > > > >
> > > > > > >
> > > > > >
> > >
> >
>
Tidak ada komentar:
Posting Komentar