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
--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> 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