[AccessD] New database design for MS SQL

No white papers.  We started rolling our own and called in a consulting
firm to review the fledgling solution and point out deficiencies.  They
did admit when they came back for a later review that they'd never seen
a Win-based .Net app this large and complex, but we aren't ready (maybe
I should say our clients aren't ready) to convert totally to web-based
yet.

We're using either SQL 2005 or Access 2000 format BE because some of our
clients are on drilling rigs in the middle of the ocean and don't have
SQL Server handy.  The embedded SQL for the provider adapters is stored
in XML files and we read it in using a streamreader and pass it back to
the calling data provider routine.  The only business logic on the
server is in cascading updates and deletes, etc., nothing else except
relationships at this point because we still have to handle both SQL
Server and Access back ends.  The business logic is in its own assembly
that's part of the overall solution.  Not sure what you mean by a
desktop design.

We create and supply software to the drilling industry--oil, gas,
geothermal--so a well is a hole in the ground plus everything associated
with that.  A wellbore, is the hole itself, which may be the original
hole in the ground or a sidetrack drilled as an offshoot of another
wellbore.

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Thursday, April 05, 2007 2:22 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] New database design for MS SQL

Hi Charlotte:

You bet... but if I asked all the questions that I want to ask the List
would reject that email due to size limitations.

Maybe some questions from the bottom or Server end. Is there any
white-papers on the design or is this the concept of some in-house
guru?...
or your-self. Is there any recommended reference material?

Can I assume you are using SQL 2005? Is the SQL data stored as XML data
or translated on the fly? Is all the business logic stored on the server
or is it managed at the FE? Can I assume a desktop design? 

These are just a few of the broad questions I would like to know further
details on... ...and what is a Well record and wellBore record or is
this something to do with wells?

That is all for now 

TIA
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Thursday, April 05, 2007 12:07 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] New database design for MS SQL

Eeek! How on earth would I do that??  

I can explain that we use a data tier that abstracts the actual data
structures by building "entity" classes that implement a typeddataset
for that data entity and interface classes that define what the data
providers will expose for the entity.  The entity/typeddataset can
address and manipulate a single table or multiple related tables
simultaneously.  We use an OleDbProvider project that houses the SQL (in
XML files) and code classes specific to a related group of tables and
their children.  The entity classes call into the data provider classes,
so the code to do a particular thing (i.e., get the next ID number for a
particular table for a particular set of parameters) is in a single
location.

We build "business rules" into the entity classes that take care of
things like returning an exception if a record is being deleted and
there are related records that need to be deleted or reassigned.  We
also use them to cascade changes/deletions/insertions to tables where it
can't be done automatically.  For instance, when we create a new Well
record, the data tier automatically creates and initial wellBore record
and doesn't allow the user to delete that wellbore except by deleting
the well.  Someday, if I ever find the time, I'm going to try modelling
this in Access!