Jim Lawrence
accessd at shaw.ca
Fri Apr 6 05:34:20 CDT 2007
Hi Charlotte: I will comment inline -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Thursday, April 05, 2007 2:46 PM To: Access Developers discussion and problem solving Subject: Re: [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. Comment: Sounds very impressive. 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. Comment: 'Desktop design' is my description of an application that has it core functionality supported at the client end; or win-based or thick client (this could also be describing the actual client?) ...As opposed to thin client application where most of my work seems to be migrating. 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. Comment: A very good explanation of your business. I would be interested in how the data tier actually works or maybe just an overview ...without divulging any trade secrets of course... like how the data tier layer abstracts the actual data structures. Is your office using the XML option in .Net datasets, through classes to connect via an OLE (third party?) to the actual tables being either MDB or MS SQL? (Handling XML in MS SQL 2005 is built-in but I was unaware that Access had the same capabilities.) I am not interested in specifics just the concepts... Maybe a bird's-eye view? Charlotte Foust MTIA Jim -----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! -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com