[AccessD] Design Considerations - Was: Table Structure Ideas

Jim Dettman jimdettman at verizon.net
Thu Sep 11 16:12:25 CDT 2014


Don,

 Yes it makes sense, although it can lead to a very complex design. For
example, there are different types of samples and every test defined may not
apply.  If we had an air sample, ppm of a contaminate would, but a slump
test would not (it's a test for concrete). So you'd need to add a table for
sample types, and then an allowed test table for each type.

 But that is indeed the correct way to do it.  If you've modeled a process
properly, then I should be able to use the system without making changes to
the database itself, only the data it contains.   A new test coming along
should not mean that I need to add a new table, queries, reports, etc, but
just more records.

 But this brings up one other point with these types of systems; do you
fully build in the knowledge of what is related to what or no?  An example
is a configurable assembly where parts A and B might go together, but C and
D cannot.   When you start building in those types of rules, then things get
very complex.

 In general I find client never want to go that far, and they leave it up
the user to determine if a "slump test" can be performed against an air
sample.

 As far as the rest:

<<Second, and more interestingly, where does one draw the line between
delivering exactly (or as near to it) what the client has asked for, and
designing for a future that even the client may not anticipate?>>

 That's a large gray area, but one of the things a client is paying you for
is your advice on how it should be done.   I always like to sit down and
explain what the ramifications are of doing it one way vs another,
especially if they want to take short cuts.

 In the end, it's their choice or course.

<<Is it ever appropriate to "out-think" the client and deliver a system that
they didn't ask for and don't know they'll be needing?>>

 I think so.   Again, their paying you to know what needs to be done.  I
wrote a small system for a health care facility about six years ago.   They
thought I was going way over board with all the stuff I brought up, but
there have been no changes since it went in.  In fact they called just a few
weeks ago about adding a new nurse and that was the first time I've heard
from them in a couple of years (they forgot where it was in the menus).
It's tracks patients, services, vaccines, billing etc and has a series of
reports to monitor all that.

 I asked how it was working and if anything was giving them a problem.  She
said "no, it's done everything we've needed it to do".

 That's when you know you've done a design right.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Thursday, September 11, 2014 01:27 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Design Considerations - Was: Table Structure Ideas

The direction of this thread interests me because it resembles my own
thought processes when evaluating different design considerations.  I don't
want to derail the conversation or muddy the water for the original poster,
but I'd be interested in hearing the opinions of others on the implications
of proposing a design approach that exceeds a client's stated requirements.

In my experience, the client often has only a vague idea about how to
structure data related to some task or problem, and usually has some narrow
view about what a user interface should look like and how it should behave.
Left to their own devices, clients would structure the data AFTER designing
their preferred UI.  They'll force the data structure to fit the UI, instead
of ensuring a sound structure.  Then, because the system was designed with a
narrow vision of its function, entirely new systems may have to be developed
(or existing ones extensively modified) to accommodate modest (and
foreseeable) changes in the operating environment.  (e.g., now we're testing
samples of material x, using tests y and z.)  This often leads to systems
performing similar work in separate silos with no integration between them.
How many times have we seen this when called in to design a solution?

In the case of the problem presented here, it seems to me that there are
three main entities that need to be modeled: Samples, Tests, and Test
Results.  In reality, there may be other entities involved with the
application, such as the Client, the Job, the Sample Site, etc., but for now
let's stick with Samples, Tests, and Results.  Given this problem, I'd be
inclined to construct a table for each of those entities, such that each
sample may be related to one or more tests, and each test may be related to
one or more results.  A subsidiary table (or tables) could contain
definitions for the various tests and constraining the types of results
relevant to each test, and the types of tests relevant to each sample type.
The advantage to this approach is that it provides a foundation for a
solution to the current problem, while accommodating the (almost) inevitable
future need to deal with new types of samples, tests, and results.  Rather
than adding new tables to accommodate ne!
 w tests and their results, we just add new records to the subsidiary tables
that define the attributes and relationships of each.  Test A may be
materially different from Test B, in terms of how it's conducted and what is
measured, but at bottom they're identical: activities that yield results
that need to be recorded and evaluated.

Now, I know that this sort of a design is a bit more complex, in terms of
its development, implementation, and ongoing maintenance.  It's not as easy
to explain to the client, either.  So I'd also be tempted to round off some
corners and compromise in the directions so far proposed.  So, I have a
couple of questions:

First, does the approach described above make sense, or am I totally
off-base?  I'm always impressed with the quality of the contributions from
AccessD posters, and, not having seen a suggestion resembling mine, I wonder
if I'm over-complicating things or missing something obvious that would
argue against it.  I often think in this way about a problem when designing
a solution, and if there's a better way, I'm all ears.
 
Second, and more interestingly, where does one draw the line between
delivering exactly (or as near to it) what the client has asked for, and
designing for a future that even the client may not anticipate?  Is it ever
appropriate to "out-think" the client and deliver a system that they didn't
ask for and don't know they'll be needing?  How do you all approach this
question?


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Wednesday, September 10, 2014 11:44 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Table Structure Ideas

You need a table for samples with a field that allows you to determine the
order.  Then you might use a table for Fibers Morphology and another for
Polarized Light Microscopy, each with the fields needed for the information
that applies.  Each of those tables would be child tables to the main Sample
table, which would contain any other information collected for a sample.
The Sample PK would be a FK in each of the child tables.

That would allow you to present the information in a variety of layouts
without straining yourself.  This assumes a one-to-one relationship between
the tables but could allow for the addition of multiple results if you added
a unique key to each child table as PK and  allowed duplicates of the parent
key.  If they wanted to add different kinds of results, you would just add
another table.

Charlotte

On Tue, Sep 9, 2014 at 11:11 PM, ACTEBS <actebs at actebs.com.au> wrote:

> Hi Everyone,
>
>
>
> Got a complex form that we're turning into a DB and having difficulty 
> coming up with a good table structure to suit the form and the way the 
> data is collected and input.
>
>
>
> They want the Access DB to mirror the form. You can see the form here:
>
>
>
> http://download.actebs.com.au/FormDoc.jpg
>
>
>
> As you can see by the example image above the sample are marked as A, 
> B, C, and D, but sometimes they display it vertically and other 
> horizontally, which is confusing the hell out of me. Any idea on how 
> best to design the table structure so the data is easy to work with down
the track?
>
>
>
> Any ideas most welcome.
>
>
>
> Thanks in advance.
>
>
>
> Vlado
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list