Writing Nearly Codeless Apps: Part 2

  • David Ziffer

    SSCommitted

    Points: 1513

    Comments posted to this topic are about the item Writing Nearly Codeless Apps: Part 2

  • Grant Fritchey

    SSC Guru

    Points: 396551

    Oh please no, not tables named TBxxx. That's just so 1992 (not to mention, very, very Access). Surely, at this point in history, we can simply name a table, or an object in code, for the business need it represents. The whole concept of Hungarian style notation should surely be dieing in this day & age.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • David Ziffer

    SSCommitted

    Points: 1513

    Grant Fritchey (10/13/2010)


    Oh please no, not tables named TBxxx. That's just so 1992 (not to mention, very, very Access). Surely, at this point in history, we can simply name a table, or an object in code, for the business need it represents. The whole concept of Hungarian style notation should surely be dieing in this day & age.

    Unfortunately in this day & age we still don't have relational databases that allow us to divide our various objects into different name spaces, so this task is left to the developer. At some point we are potentially going to want to name one thing "table X" and another thing "view X", and the only difference in the names will be whatever we choose to designate something as being a "view" vs. "table". If we choose nothing to designate tables from views, then we have to start making arbitrary name choices in order to accomplish this, but the whole point of RAP is consistency, and its code generators never make arbitrary choices.

    With regards to the "xxx": this is no different that putting things into .NET name spaces. Once again, the relational database does not give us any tools for compartmentalizing our tables, so we must unfortunately do it ourselves. Presumably if we object to this convention (or at least some convention for accomplishing this) then we'd also prefer the good old days of VB6, where every global object was in the same gigantic name pool.

    The design decisions of RAP were not made with regards to what was or is in or out of fashion. Rather they were designed to give RAP maximum capability for managing database object names with a maximum of regularity and a minimum of arbitrary variation.

  • Grant Fritchey

    SSC Guru

    Points: 396551

    I'm OK with the xxx part. Sorry that was just meant to designate '*' or 'whatever' or 'fillintheblank.' My only exception was with the Hungarian notation on table names.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • roger.plowman

    SSChampion

    Points: 10243

    Ok, I have a problem with the approach from the word go, and it's this:

    "implement "ideal" auditing, where the app itself can recall all versions of the data that were ever entered into it;"

    *All* versions? 🙂

    You do realize this is physically impossible, yes? The storage requirements for a CRM system are already astronomical, and that's just for the *current* state of the data. Add in a requirement that you keep EVERY SINGLE CHANGE (presumably reversibly) and you've moved from engineering to magic.

    Given that, the rest of the approach is seriously suspect.

    Unless, of course, I misunderstood "all versions of data ever entered"...

  • David Ziffer

    SSCommitted

    Points: 1513

    roger.plowman (10/13/2010)


    Ok, I have a problem with the approach from the word go, and it's this:

    "implement "ideal" auditing, where the app itself can recall all versions of the data that were ever entered into it;"

    *All* versions? 🙂

    You do realize this is physically impossible, yes? The storage requirements for a CRM system are already astronomical, and that's just for the *current* state of the data. Add in a requirement that you keep EVERY SINGLE CHANGE (presumably reversibly) and you've moved from engineering to magic.

    Given that, the rest of the approach is seriously suspect.

    Unless, of course, I misunderstood "all versions of data ever entered"...

    RAP is intended for use primarily by small to medium projects, so given today's enormous storage capacities, storing all versions of every record ever deleted isn't really that big of an issue. But aside from that, RAP uses two distinct sets of tables: the primary or "current" tables holding current data, and the "archive" (or "audit" or "shadow" tables) in which all past versions of records are stored. At some point a large project could decide that all data beyond a particular date is forever obsolete, and it could simply delete that data from the archive tables. So even for a large project, RAP could easily handle the space-constraint problem because it neatly segregates its past data for you. And since RAP keeps only current data in its "current" tables, your app never has to wade through past data to find current data.

    I have worked on several large projects where the project is informally storing every version of every record ever entered in many of its tables, but it's always done badly. Every one of these projects has accomplished this by using "effective date" fields or "deleted" flags. These mechanisms are horrendous because 1) every query that looks into any table has to account for the fact that there are possibly multiple versions of every record and it has to find the latest (or valid) one, 2) the presence of such data requires that keys and indexes accommodate this "archiving" (rather than representing the natural current structure of the problem) and 3) of course the performance is a disaster because in addition to every query having to do the extra query work described in "1" above, the vast majority of data in the tables is obsolete, yet the server must look through it on every single query for current data (which is 99% of the queries).

    RAP eliminates the arbitrary nature of deciding which tables get audited and which don't. It eliminates the need for developers to design specialized auditing mechanisms (both storage and retrieval) because it implements both the storage and retrieval mechanisms for you automatically. It separates current from past data, making it both easy and efficient to either keep or dispose of the past data as you wish.

    So please, stick with the series and give this a chance. It is really well thought out. I don't think I've missed a trick here, and at the end I'm hoping you'll agree.

  • mmorley

    SSC Rookie

    Points: 35

    I agree with many of your comments, especially on naming conventions. Naming conventions are totally arbitrary and are meant to allow the team to work together with minimal communication issues. I am consultant and each project we get the team to agree to naming standards before we start. They are necessary and what works for the team is acceptable.

    And now the big BUT :-), after over 40 years of development I have learned you do not name data elements based on their attributes ... never. I am referring to your DR, DT notations. How much effort is required to change the application if a column is changed from a nullable to not nullable or the other way around? Where are all the column references? How about the references outside RAD like reporting and BI applications and their meta-data? I am not saying you can't manage this, but why introduce the risk?

    Overall, good job and good ideas. Many low cost UML tools implement this philosophy and provide for standard name generation, key structures, DOM, etc. and they have the ability to support multiple DBMSes and application environments (i.e. .net vs java, IIS vs others, etc).

  • mmorley

    SSC Rookie

    Points: 35

    The auditing is a requirement for many systems today. Can you say SOX or HIPAA? Disk is cheap, this is one of the major strengths of concept.

  • richardd

    Hall of Fame

    Points: 3899

    David Ziffer (10/13/2010)


    With regards to the "xxx": this is no different that putting things into .NET name spaces. Once again, the relational database does not give us any tools for compartmentalizing our tables, so we must unfortunately do it ourselves.

    Ever heard of schemas?

    MSDN: http://msdn.microsoft.com/en-us/library/ms365789.aspx


    A schema is a container that holds tables, views, procedures, and so on. … Every securable in a specific schema must have a unique name. The fully-specified name of a securable contained by a schema includes the name of the schema that contains it. Thus, a schema is also a namespace.

    David Ziffer (10/13/2010)


    At some point we are potentially going to want to name one thing "table X" and another thing "view X", and the only difference in the names will be whatever we choose to designate something as being a "view" vs. "table".

    If that's the case then your database design sucks. If there's a table called "Customers", why would there ever be a view called "Customers" in the same schema? It's pointless and confusing.

  • deanroush

    SSC-Addicted

    Points: 474

    First of all, I applaud you for sharing your theory (and effort) related to design standardization such that it will permit automated code generation. I am also developing a custom framework because I do not believe the existing models and tools go far enough in some areas and because of some custom requirements in our business model. I guess that means I am re-inventing the wheel on a massive scale but the ROI is there for us.

    For object (table, view, proc, etc) naming conventions you might consider using schemas instead of Hungarian notation. I realize not all RDBMSs support schemas equally but we sure take advantage of them in our SQL2008 environment.

    I look forward to reading your future posts in this regard.

  • David Ziffer

    SSCommitted

    Points: 1513

    richardd (10/13/2010)


    [quote-0If that's the case then your database design sucks. If there's a table called "Customers", why would there ever be a view called "Customers" in the same schema? It's pointless and confusing.[/quote-0]

    It is entirely possible that an application would want to have consistent views of essentially all its tables that exclude things like status fields. So if I have an app where I need an entire set of views that correspond to essentially all the tables, I'd have to distinguish their names somehow. Better to do it consistently than inconsistently.

    Beyond this there is a practical example that RAP actually uses (this is explained two or three articles down the road BTW). For every single primary key, foreign key, and unique constraint or index in each table, RAP generates a set of user-functions and a corresponding set of stored procedures that allow you to look up records in the table according to the fields of that key or index. The user functions are intended for use in any hand-written queries you may want to write (primarily for reports), whereas the stored procedures are intended for use by the application's data layer.

    The user function and the corresponding stored procedure are identical except that one is a user function and the other is a stored procedure. So there's a choice here: invent some algorithm for inconsistently modifying the generated names of otherwise identical objects, or simply have a consistent naming convention for distinguishing them. RAP is all about consistency, so it uses the latter.

    Examples of function / procedure pairs that RAP generates: For the table "TBadmUser" in the canned "ExampleCRM" demo that comes with RAP, RAP generates these two routines for looking up records via the primary key:

    UFTBadmUser#PK (user function)

    SPTBadmUser#PK (stored procedure)

    Why does RAP generate these routines, you might ask? Well the main reason is that RAP fully audits everything, and it gives you everything you need to retreive all data, both past and present. Unlike code you might write yourself, these routines let you choose (by passing either a date or a null) whether you are going to be retrieving current vs. archived data. All this will be covered later in the series.

  • trubolotta

    SSC Veteran

    Points: 219

    A very interesting article. I have used variants of some of these suggestions and ideas in my own designs, plus a few of my own invention with extremely good results. The results have been applications that get done on budget, on time, are highly adaptable and have very simple maintenance requirements. That has proven to me that applications that do extremely complex things need not be complex or undecipherable. The article is a great swipe at the historical trend toward what I call "spaghetti and meatball" architecture.

  • noeld

    SSC Guru

    Points: 96590

    I used to build stuff this way loooooong time ago.

    I would advice the author to look in to ORM products (that many are free) which contain MANY specifications and build everything posted here (and much more) from XML description files.

    Just my $0.02

    BTW my objections are only two:

    - Please do NOT use Hungarian for database objects

    - There is nothing wrong with not distinguishing between view and table in fact that is the point of using views.

    Other than that I think people enrich their knowledge by observing different approaches to the same problem.


    * Noel

  • ismell

    SSC Enthusiast

    Points: 130

    Great series! I've been doing code gen for the past year and I love it. It is true RAD. As for the column naming, why does everyone care so much ? He said it was controversial. If you don't like it just change your templates if needed and go on with your day.

  • David Webb-CDS

    SSCoach

    Points: 17338

    Unfortunately in the real world these important things constitute only a small fraction of developers’ time, because most developers spend most of their time:

    reinventing data modeling (i.e. choosing from among too many modeling techniques)

    reinventing database access on the database side

    reinventing database access on the application side

    reinventing business rule framework technology

    diddling with endless details of UI component management

    and reinventing UI design (i.e. choosing from among too many modeling techniques).

    Really? Do you have any stats/research that backs up that assertion? I'm not an expert on what developers do but I have extensive experience as a DBA and managing groups of DBA's and I have to say that we spent very little time re-working our modeling techniques. We had standard tools and techniques. We used them. Once in a blue moon we re-examined our methods agains the latest and greatest tools and techniques and adjusted as necessary.

    I'm not sure what 'reinventing database access' means. We had some utility programs that generated CRUD code for tables and/or views if we needed it, but that was a very small portion of the work done by the DBA staff.

    We did have standards about how the DBMS was used. We had those standards because often we had little control over how the development groups wrote their applications. We opted to put the business logic near the data to help ensure consistency across the application portfolio. It worked.

    So, from my perspective, you're solving a problem that may not really exist on the database side.


    And then again, I might be wrong ...
    David Webb

Viewing 15 posts - 1 through 15 (of 43 total)

You must be logged in to reply to this topic. Login to reply