Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!news-out.nuthinbutnews.com!propagator2-sterling!news-in.nuthinbutnews.com!news.csl-gmbh.net!newsfeed.r-kom.de!fu-berlin.de!uni-berlin.de!userea238.dsl.pipex.COM!not-for-mail From: "Tobin Harris" Newsgroups: comp.databases.theory Subject: Re: Client server design question. Date: Mon, 19 May 2003 13:31:19 +0100 Lines: 97 Message-ID: References: NNTP-Posting-Host: userea238.dsl.pipex.com (62.188.32.238) X-Trace: fu-berlin.de 1053347485 28075353 62.188.32.238 (16 [135366]) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Xref: core-easynews comp.databases.theory:26475 X-Received-Date: Mon, 19 May 2003 05:30:50 MST (news.easynews.com) "sello" wrote in message news:c97b9fa.0305150005.10c54b11@posting.google.com... > Has anyone had experience with a similar system to the following, or > can offer some pro's and con's to the argument. > > My company is recently undertaking a major project to develop a > database driven accounting system. The current design sees a > relational database backend with a data access layer in C# through > which client programs access the database. The data access layer will > contain all the functionality that would otherwise be in stored > procedures and all programs are supposed to get/put data to the > database via this data access layer. The RDBMS does support stored > procedures quite well. > > The whole system essentially is comprised of multiple pc programs > accessing a central database driven program via a WAN. > > Essentially the database is treated as a set of tables with > relationships. > > The downsides to this approach that I see are: > 1) The data model and business logic will be split between tables, > relationships, some triggers and the external data access layer > written in c#. > 2) Possible consistancy problems in the data model as the external > code may refer to views that don't exist etc. > 3) The RDBMS would not be able to optimise the functionality that has > been moved to an external c# program, whereas with stored procedures > it can. > > Pro's > 1) Easier to move to a different RDBMS as the stored procedure syntax > and capability varies between vendors. > 2) Richer object model using c#. > > > Any thoughts My recent expereinces may be of use (although I'm no pro!). Recently, I've been tasked to further development of a companies software. Their set us is as follows: - Central SQL Server database accessible over web - 3 Web sites/systems that read/write the data - 4 VB applications that read/write data (so 7 clients in total) - Clients us a mixture of direct table access and also call Sprocs. - A given VB app may use several approaches in data access - Data Evironments, direct recordset usage etc Now, this is causing me big problems! In a nutshell, the problems are mostly related to dependency management, or answering the question "when I change this what will I break?". The mess I'm working with is making it very hard to push the project forward, and data-access is key. IMHO, its important to ensure that app->database dependencies are vey clear and hopefully centralised, nomatter which approach you finally chose. From your post I think you're on to this anyway, but are torn between using a rich middle tier, or just going in via sprocs. From my own experiences, I would look to start with sprocs that do the grunt work You could then have a thin data access library that your apps can use - I'd be keen to make sure they all use the same DLL though. If you find that there's functionality that the clients need that isn't much to do with the database, then you could look at creating some middle tier components to wrap this up, again as a DLL that can be shared. I suppose what I'm saying is start light, and add middle tier complexity as and when needed. > 1) The data model and business logic will be split between tables, > relationships, some triggers and the external data access layer > written in c#. I always find this bit difficult! I suppose I don't mind distributing business rules through different tiers, as long it's clear why you've done it. For example, a project we worked on had a fairly light data access tier that apps used directly. However, there were areas where sproc wrappers simply were'nt enough. We were working with recursive tree structures and found that if we make our own objects to faciliate navigation of the tree, it was much easier, and all client apps could use it. > 2) Possible consistancy problems in the data model as the external > code may refer to views that don't exist etc. I think you'll always get this, becuase code always needs to be updated to reflect changes in the database. However, if all your data-access is performed through a shared DLL, rolling out changes shouldn't be too hard. You may want to check out Martin Fowlers "Patterns of Enterprise Application Development", this book talks about various degrees of these architectures. Hope this helps, Tobin > Sello > > Apologies if this is the wrong forum.