TY Crystal, I shall cogitate on your response. I particularly want to ty for going through my list of questions and answering each one individually. That helped a great deal.
So So many business perspectives to consider in the real life. Thinking in terms of current needs and guessing at what is down the pipe in the heads of imagineers (i know it's not a real word).
I thank you, John, Duane, for contributing, and continue to solicit comments from all who wish to provide me with Their 2 Cents :)
Terence
--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi Terence,
>
> > "I do good document,
> > but generally at the end of the project"
>
> Do you mean the relationship diagram? This is not documentation, a good diagram is essential to a smooth database
>
> > "Is there a significant disk space savings"
>
> why is this criteria? Hard drives are a lot cheaper now than 20 years ago. If you risk exceeding the 2 gig limit of Access, examine the length of text fields (make them no longer than necessary), put memo fields into a linked table in a different back-end, and if you cannot optimize the structure any more then use multiple back-ends.
>
> > "2. Is there a significant reduction of access time in record retrieval, No one has answered that yet."
>
> Once again, this is not criteria for changing structure. It is what is is. Oftentimes, reporting requirements may be such that a reporting database is generated nightly to enable more criteria and enhance performance of reporting.
>
> > "Does it make things simpler, probably not. "
>
> If you already have an answer, then you are not listening ...
>
> > "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."
>
> There could be a field for VisitID. If it is filled, then it is relevant.
>
> > "usability and portability for my statisticians and clinical researchers... that particular survey"
>
> just because questions are only used on one set of data does not mean that the data should be specific to the analysis that will be used...
>
> perhaps you will store things generically and create data warehouse tables for easier advanced equation analysis ... and/or put results into Excel
>
> > "abstinent or belligerent"
>
> absolutely not, Terence, you are fine ;)
>
> > "make GOOD decisions "
>
> as we want for you too
>
> > "Just because this schema may seem "different", or "Unorthodox", does not mean that it wont work. "
>
> the closer you pattern data to the simplicity of life (which is not easy to see), the more flexible things will be. You will be able to write code for one thing and modify it only slightly to accommodate a seemingly different request. The trick is seeing the basic relationships and the underlying simplicity. the answer is not to make another table ... it is to adapt the structure you already have for a new goal.
>
> Study Duane's sample survey application. His has a great mind and has put thought to this ... try the steps I gave you for documenting a database. Don't spend 2 hours -- put aside an entire day to study his structure and understand it. Use my analyzer reports to help you.
>
> www.AccessMVP.com/strive4peace/Dictionary
>
> > "I am not there yet :)"
>
> that is ok ;) ... sleep helps ...
>
>
> Warm Regards,
> Crystal
>
> *
> (: have an awesome day :)
> *
>
>
>
> --- On Sat, 8/27/11, Terence wrote:
>
> > 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