[AccessD] Design Considerations - Was: Table Structure Ideas

McGillivray, Don DMcGillivray at ctc.ca.gov
Fri Sep 12 11:16:21 CDT 2014


Thanks to all for your responses.

I've been working with Access off and on for over 15 years - mostly as an employee within larger organizations.  My knowledge of database design has been acquired mostly through trial and error (with emphasis on the error) along with a reliance on trusted resources - AccessD being chief among them.  Given my seat-of-the-pants training, I appreciate the validation of my understanding.  It's also encouraging to learn that there's precedent for the idea of designing for what's needed - in spite of what's been requested.

Don


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

Great post Don - I am going to file this one as I have seen and experienced many of these issues and questions in my professional life.

As for "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?"

For many clients I have worked with it is nearly always this way.  It is not their fault though as they think of data in terms of the front end UI forms (almost like MS Word visual frame) and have little to no understanding of data integrity, scalability and the ease of updating for future changes - and there are always changes.

It is usually worth spending a fair bit of time talking to the client about their needs and watching how folks use the interface before starting.  Having a deeper understanding about how the data is used and how it integrates into their organisation and systems usually pays big dividends.

As ol' Abe Lincoln was reported to have said "Give me six hours to chop down a tree and I will spend the first four sharpening the axe."

So I would say 'yes' in the majority of cases.  Trust your experience and instincts - make the front end just as the like it, but make sure the back end is built as you need it to be, not as the client thinks it should be - they are usually completely out of their depth here.

Cheers
Darryl

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don
Sent: Friday, 12 September 2014 3:27 AM
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

--
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