Professional Web Applications Themes

Your comments please.. - Microsoft SQL / MS SQL Server

I am putting this NOTE since 3 days. Many people are sharing their comments and experience. Your comments and experience are also welcome: And if I give a example to support the following note, there is package SOLOMON. It's Microsoft product. This software stores the data in two databses of SQL Server 7.0 or 2000 (1) SYSTEM DATABASE (2) APPLICATION DATABASE. There are about 1500 tables in these two databases. No table level constaints, no coulmn level constraints, no triggers, etc. have been defined at database level. The ingerity of database is matained by the front-end application (Solomon Kernel) written ...

  1. #1

    Default Your comments please..

    I am putting this NOTE since 3 days. Many people are
    sharing their comments and experience. Your comments and experience are also
    welcome:

    And if I give a example to support the following note,
    there is package SOLOMON. It's Microsoft product. This
    software stores the data in two databses of SQL Server 7.0
    or 2000 (1) SYSTEM DATABASE (2) APPLICATION DATABASE.
    There are about 1500 tables in these two databases. No
    table level constaints, no coulmn level constraints, no
    triggers, etc. have been defined at database level. The
    ingerity of database is matained by the front-end
    application (Solomon Kernel) written in Visual Basic.


    ***************

    When we develop a package using front end like Visual
    Basic etc. then we can write code to validate what kind of
    data the user can enter, save, update or delete. For
    example, if we want the Name should not be greater than 20
    characters, we can write code for this validation. If we
    want that EmpID should be unique, we can write code for
    this. If we want that Salary should always be a positive
    number. I mean we can write code to completely manage how
    the data should be delete/saved/updated in our database.

    Now my questions is then why do we have to the double
    work. Then where is the need to define the constraints
    (column level or table level) in the database tables if
    everything we can do in the Front-end development
    language. Also, even we if we define the constraints in
    the database tables still to check whether there is any
    violation of those constraints we have do write code in
    front-end application at least to trap the error to know
    constraint failed?

    **************


    Sender Guest

  2. #2

    Default Re: Your comments please..

    > Now my questions is then why do we have to the double
    > work. Then where is the need to define the constraints
    > (column level or table level) in the database tables if
    > everything we can do in the Front-end development
    > language.
    Because the front end could have errors, you might miss things in your
    logic, and people can get around it.


    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: Your comments please..

    Solomon's database is a poor example of proper database design. It was
    ported directly from Btrieve (I think) and no time was spent re-architecting
    it for SQL Server. It's probably a good case study for how NOT to design a
    database. I'm not necessarily advocating the user of triggers. However,
    the lack of DRI and other types of constraints is inexcusable IMO.

    J.R.
    Largo SQL Tools
    The Finest Collection of SQL Tools Available
    [url]http://www.largosqltools.com[/url]

    "Sender" <userdomain.com> wrote in message
    news:ewN3zNvRDHA.3132tk2msftngp13.phx.gbl...
    > I am putting this NOTE since 3 days. Many people are
    > sharing their comments and experience. Your comments and experience are
    also
    > welcome:
    >
    > And if I give a example to support the following note,
    > there is package SOLOMON. It's Microsoft product. This
    > software stores the data in two databses of SQL Server 7.0
    > or 2000 (1) SYSTEM DATABASE (2) APPLICATION DATABASE.
    > There are about 1500 tables in these two databases. No
    > table level constaints, no coulmn level constraints, no
    > triggers, etc. have been defined at database level. The
    > ingerity of database is matained by the front-end
    > application (Solomon Kernel) written in Visual Basic.
    >
    >
    > ***************
    >
    > When we develop a package using front end like Visual
    > Basic etc. then we can write code to validate what kind of
    > data the user can enter, save, update or delete. For
    > example, if we want the Name should not be greater than 20
    > characters, we can write code for this validation. If we
    > want that EmpID should be unique, we can write code for
    > this. If we want that Salary should always be a positive
    > number. I mean we can write code to completely manage how
    > the data should be delete/saved/updated in our database.
    >
    > Now my questions is then why do we have to the double
    > work. Then where is the need to define the constraints
    > (column level or table level) in the database tables if
    > everything we can do in the Front-end development
    > language. Also, even we if we define the constraints in
    > the database tables still to check whether there is any
    > violation of those constraints we have do write code in
    > front-end application at least to trap the error to know
    > constraint failed?
    >
    > **************
    >
    >

    Largo SQL Tools Guest

  4. #4

    Default Re: Your comments please..

    Yep, eventually someone _will_ import data from other systems into your
    database. And that will go through Bulk Insert or DTS or something and not
    through the Front end application. :-)

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:ew2vTjvRDHA.2332TK2MSFTNGP10.phx.gbl...
    > > Now my questions is then why do we have to the double
    > > work. Then where is the need to define the constraints
    > > (column level or table level) in the database tables if
    > > everything we can do in the Front-end development
    > > language.
    >
    > Because the front end could have errors, you might miss things in your
    > logic, and people can get around it.
    >
    >

    Jacco Schalkwijk Guest

  5. #5

    Default Re: Your comments please..

    > Yep, eventually someone _will_ import data from other systems into your
    > database. And that will go through Bulk Insert or DTS or something and not
    > through the Front end application. :-)
    Or, some witty user will open query yzer and run inserts/updates etc.
    Little the front end application can do to apply its "only needed in one
    place" validation...


    Aaron Bertrand - MVP Guest

  6. #6

    Default Re: Your comments please..

    >> Now my questions is then why do we have to the double work. <<

    We don't have to! In general, poorly designed database systems put
    unnecessary work on the users forcing them to implement many data related
    rules on the client side. Obviously client side validations must be handled
    on the presentation tier. As such with the data, a well-implemented system
    should handle all the relevant business rules using the DBMS.

    A business rule is a directive that asserts or constrains some aspect of the
    business. It can be defined as a formalized representation of a real world
    constraint, expressed in Boolean logic that can be evaluated to true/success
    within a given context. Thus all predicates, functional dependencies,
    domains/types, integrity constraints, query logic etc falls into this
    category.

    However, not all commercially available DBMS are well equipped with suitable
    tools & structures for handling every business rules and thus it forces the
    user to leverage the power of client software to implement some of them. Due
    to apathy, ignorance, lack of well-scribed standards and other commercial
    factors driving the IT markets, there is no clear definition of rules as to
    why, when & how a rule should be implemented. This obviously leads to, what
    you call the "double work".

    From a quality perspective, it is quite possible that one can come up with a
    consistent & standard way of defining business rules in an organization &
    how they should be implemented. Adhering to such standards (whether formal
    or informal) most likely help in avoiding massive re-coding on multiple
    tiers. Of course, then you start worrying about other diseases like scope
    creeps & req. alterations etc, which is quite common in practical
    implementations :-)

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  7. #7

    Default And another thing ...

    Here is a real world example of why the database needs to have some
    integrity checks built in.

    I am consolidating event logs from about 70 of our customers into a single
    reporting database (with helpful input from experts here!). The solution I
    used works great. Sort of. As it turns out, some of the assumptions about
    the data are wrong, or more correctly, those assumptions about the data were
    violated by the front end. The first pass through the consolidation resulted
    in a million records, with about 40,000 that were not as expected. After
    some further scrubbing of the data, I got this down to about 7,000 defective
    records (less than 1%). Fortunately, this is not a critical issue for the
    task at hand and the bad records are of little consequence. We will fix the
    front end and future consolidations will work as expected. But if this had
    been a mission critical task, then there would have been some serious
    panicking by everybody. The point is that if the database enforced its own
    data integrity rules, then it would have saved me time and aggravation. In
    terms that your managers can understand TIME=MONEY. Offloading expenses into
    to post release maintenance by not doing double work may seem like a good
    idea. But it will come back to haunt somebody.

    If this doesn't make it clear to you, then I'm not sure what will.

    Good luck!

    Bob Castleman
    SuccessWare Software



    "Sender" <userdomain.com> wrote in message
    news:ewN3zNvRDHA.3132tk2msftngp13.phx.gbl...
    > I am putting this NOTE since 3 days. Many people are
    > sharing their comments and experience. Your comments and experience are
    also
    > welcome:
    >
    > And if I give a example to support the following note,
    > there is package SOLOMON. It's Microsoft product. This
    > software stores the data in two databses of SQL Server 7.0
    > or 2000 (1) SYSTEM DATABASE (2) APPLICATION DATABASE.
    > There are about 1500 tables in these two databases. No
    > table level constaints, no coulmn level constraints, no
    > triggers, etc. have been defined at database level. The
    > ingerity of database is matained by the front-end
    > application (Solomon Kernel) written in Visual Basic.
    >
    >
    > ***************
    >
    > When we develop a package using front end like Visual
    > Basic etc. then we can write code to validate what kind of
    > data the user can enter, save, update or delete. For
    > example, if we want the Name should not be greater than 20
    > characters, we can write code for this validation. If we
    > want that EmpID should be unique, we can write code for
    > this. If we want that Salary should always be a positive
    > number. I mean we can write code to completely manage how
    > the data should be delete/saved/updated in our database.
    >
    > Now my questions is then why do we have to the double
    > work. Then where is the need to define the constraints
    > (column level or table level) in the database tables if
    > everything we can do in the Front-end development
    > language. Also, even we if we define the constraints in
    > the database tables still to check whether there is any
    > violation of those constraints we have do write code in
    > front-end application at least to trap the error to know
    > constraint failed?
    >
    > **************
    >
    >

    Bob Castleman Guest

  8. #8

    Default Re: Your comments please..

    > However, not all commercially available DBMS are well equipped with
    suitable
    > tools & structures for handling every business rules and thus it forces
    the
    > user to leverage the power of client software to implement some of them.
    I don't think this is the only reason...

    For example, think of two text fields on a web page, where a user can enter
    a starting and ending date for a report they'd like to render. Before even
    bothering passing those dates to the database, it is more efficient on the
    client side to ensure:

    (a) that the dates are dates and not numbers or words
    (b) that the starting date is <= the ending date
    (c) that the ending date is <= today

    The database can then check to make sure the date ranges are valid in the
    context of the business rules and/or data.

    This prevents wasted network traffic, and the user from waiting for the
    database to tell them that 13/13/2046 is not a valid date for which to start
    a report. :-)


    Aaron Bertrand - MVP Guest

  9. #9

    Default Re: Your comments please..

    I thought, I mentioned the same point about client side validations in the
    first paragraph of my response.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  10. #10

    Default Re: Your comments please..

    > I thought, I mentioned the same point about client side validations in the
    > first paragraph of my response.
    You did... I meant to include more data-specific validations you could do on
    the client, for example if you know that the earliest date in your database
    is 2003-01-01, you can prevent the user from trying to generate a report
    that falls in 2002. You could also retrieve a list of reports that have
    already been generated from the database, and store them in a client-side
    array, and have the web page redirect to an existing report instead of
    making the database generate it all over again. Obviously there are
    limitations to this specific example, but I'm sure if it wasn't the end of
    my work day I could think of a dozen more validation items that could be
    handled on *both* client and server.

    All I'm saying is that there can be more to it than apathy or ignorance; for
    example, convenience. :-)

    A


    Aaron Bertrand - MVP Guest

Similar Threads

  1. Disable Options>Import Comments in Comments Pane
    By Joeri_Paeleman@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: August 8th, 01:02 PM
  2. Comments if you please..
    By chameleonmoments in forum Macromedia Flash Sitedesign
    Replies: 6
    Last Post: February 15th, 06:00 PM
  3. Comments please
    By jiggs webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 2
    Last Post: January 10th, 12:51 AM
  4. any comments welcome
    By DayusRam webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 5
    Last Post: January 6th, 12:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139