Loading ...
Sorry, an error occurred while loading the content.
 

Redesign of Enterprise Schema

Expand Messages
  • jmikres
    I had a question about how to structure the database for a rewrite of a legacy back-end system. The legacy system is based on one very large database schema.
    Message 1 of 6 , Sep 17, 2008
      I had a question about how to structure the database for a rewrite of a legacy back-end
      system. The legacy system is based on one very large database schema. This legacy system
      processes all aspects of the back-end i.e. Order Entry, Order Processing, Product Catalogs,
      Inventory, etc. Since the rewrite of the legacy system will be structured and built in an agile
      and iterative manner - we've discussed implementing the system as separate applications
      that comprise logically grouped functionality - such as Product Processing, Inventory, Order
      Entry, etc. Would it make sense to restructure and design the one huge database into
      multiple schemas so the new applications could have independent schemas? Or would you
      leave it as one schema that the separate apps would just map to being responsible for
      subsets of the tables?

      Thanks,

      Jeff
    • David Lawrence
      Michael Stonebraker, in an essay in Readings in Database Systems comes down strongly against shared nothing architectures for an E-commerce site. This was
      Message 2 of 6 , Sep 17, 2008
        Michael Stonebraker, in an essay in "Readings in Database Systems" comes down strongly against "shared nothing" architectures for an E-commerce site. This was the approach that Amazon took and they are having great difficulties refactoring their database to include simple ERP logic.

        Having worked on an Agile project, I am firmly convinced that there is no such thing as refactoring a database while it is in the midst of development. Too many hindrances: schema changes, data migration, rewriting of stored procedures, etc. while the devlopment team is trying to move forwards and add new functionalities.

        Conclusion: Use a single schema on a clustered database or grid and solve the big picture first before plunging into schema design. A well-formulated conceptual model would help greatly on achieving a robust, extensible design.

        David

        David Lawrence | President, MVF Associates, Inc. | Enterprise Data Architecture Services | Office: 617-576-1864







        ----- Original Message ----
        From: jmikres <jmikres@...>
        To: agileDatabases@yahoogroups.com
        Sent: Wednesday, September 17, 2008 11:23:07 PM
        Subject: [agileDatabases] Redesign of Enterprise Schema


        I had a question about how to structure the database for a rewrite of a legacy back-end
        system. The legacy system is based on one very large database schema. This legacy system
        processes all aspects of the back-end i.e. Order Entry, Order Processing, Product Catalogs,
        Inventory, etc. Since the rewrite of the legacy system will be structured and built in an agile
        and iterative manner - we've discussed implementing the system as separate applications
        that comprise logically grouped functionality - such as Product Processing, Inventory, Order
        Entry, etc. Would it make sense to restructure and design the one huge database into
        multiple schemas so the new applications could have independent schemas? Or would you
        leave it as one schema that the separate apps would just map to being responsible for
        subsets of the tables?

        Thanks,

        Jeff



        [Non-text portions of this message have been removed]
      • Tim B
        I think the answer to your question is dependent on how your application access the data.  If there are a lot of tables used in common then it makes sense to
        Message 3 of 6 , Sep 19, 2008
          I think the answer to your question is dependent on how your application access the data.  If there are a lot of tables used in common then it makes sense to keep everything in the schema.  If the applications use distinct sets of tables then multiple schemas makes more sense and gives you more options as far as moving the different schemas to separate database servers goes.

          If you do end up with a single schema it may be possible to differentiate the applications table through a naming convention.  In the past given your situation I have given table a trhee letter prefix based on the main application that uses the table, and of course there are always an number of common tables used across all/most applications.

          Tim Boettcher




          [Non-text portions of this message have been removed]
        • Tim B
          I ve had a different experience than David.  The Agile team I am know on is developing three applications concurrently with two already in production.  All
          Message 4 of 6 , Sep 19, 2008
            I've had a different experience than David.  The Agile team I am know on is developing three applications concurrently with two already in production.  All these applications have their own schema, in addition there is a common schema that contains data shared by all applications.

            We have about 20 developers, 18 system testers and there are about 8000 DBunit/Junit tests in each program.  We do all of commits using TeamCity's delayed commit function and our build is broken less than 10% of the time.  In the last eight months I have done 2 major refactorings of the database and totally revamped the database build process twice in addition to numerous small changes.  Each developer has a database on their workstation adndthe system testers have 54 test database environments (as of today, it varies).

            Because of our large amount of legacy test data (never mind production data) all day to day database changes and the refactorings have to take data migration into account.  And it all has to be accounted for in a incremental basis of our normal build.

            So I think my experience shows that you can do major database refactoring while in development, but your environment and tools have to set up to support the effort.

            Tim Boettcher

            >Posted by: "David Lawrence"
            david_s_lawrence@...
              david_s_lawrence


            >Wed Sep 17, 2008 10:16 pm (PDT)


            [snip]

            >Having worked on an Agile project, I am firmly convinced that there is
            no such thing as
            >refactoring a database while it is in the midst of
            development. Too many hindrances:
            >schema changes, data migration, rewriting of stored procedures, etc. while the devlopment
            >team is trying to move forwards and add new functionalities.


            [snip]





            [Non-text portions of this message have been removed]
          • jmikres
            Tim, Do the separate apps that you re creating all map to that common schema through an ORM like hibernate? Do you have any constraints between the specific
            Message 5 of 6 , Sep 19, 2008
              Tim,

              Do the separate apps that you're creating all map to that common
              schema through an ORM like hibernate? Do you have any constraints
              between the specific schemas and the common schemas that you enforce?
              That was my biggest concern with having both specific and common
              schemas that are shared by applications. Or do you access the common
              schemas data through services? Its good to hear that you've had
              success with this type of architecture.

              jeff

              --- In agileDatabases@yahoogroups.com, Tim B <boettt@...> wrote:
              >
              > I've had a different experience than David.  The Agile team I am
              know on is developing three applications concurrently with two already
              in production.  All these applications have their own schema, in
              addition there is a common schema that contains data shared by all
              applications.
              >
              > We have about 20 developers, 18 system testers and there are about
              8000 DBunit/Junit tests in each program.  We do all of commits using
              TeamCity's delayed commit function and our build is broken less than
              10% of the time.  In the last eight months I have done 2 major
              refactorings of the database and totally revamped the database build
              process twice in addition to numerous small changes.  Each developer
              has a database on their workstation adndthe system testers have 54
              test database environments (as of today, it varies).
              >
              > Because of our large amount of legacy test data (never mind
              production data) all day to day database changes and the refactorings
              have to take data migration into account.  And it all has to be
              accounted for in a incremental basis of our normal build.
              >
              > So I think my experience shows that you can do major database
              refactoring while in development, but your environment and tools have
              to set up to support the effort.
              >
              > Tim Boettcher
              >
              > >Posted by: "David Lawrence"
              > david_s_lawrence@...
              >   david_s_lawrence
              >
              >
              > >Wed Sep 17, 2008 10:16 pm (PDT)
              >
              >
              > [snip]
              >
              > >Having worked on an Agile project, I am firmly convinced that there is
              > no such thing as
              > >refactoring a database while it is in the midst of
              > development. Too many hindrances:
              > >schema changes, data migration, rewriting of stored procedures,
              etc. while the devlopment
              > >team is trying to move forwards and add new functionalities.
              >
              >
              > [snip]
              >
              >
              >
              >
              >
              > [Non-text portions of this message have been removed]
              >
            • Tim B
              Jeff, We are using Oracle 10g.  To clarify what I m saying: a database consists of the Oracle software and the data.  A schema is a logical namespace within
              Message 6 of 6 , Sep 20, 2008
                Jeff,

                We are using Oracle 10g.  To clarify what I'm saying: a database consists of the Oracle software and the data.  A schema is a logical namespace within a database.

                >Do the separate apps that you're creating all map to that common
                >schema through an ORM like hibernate?

                We use Hibernate for almost all data access.

                >Do you have any constraints

                >between the specific schemas and the common schemas that you enforce?

                No constraints between the schemas.  The schemas may be housed in separate databases making constraints very unwieldy,  so all access is through a separate connection.  Relation integrity is very carefully checked/enforced by the application.

                >That was my biggest concern with having both specific and common

                >schemas that are shared by applications. Or do you access the common

                >schemas data through services?

                In an upcoming application (development starts mid-October) we are introducing a new common data source
                and we plan to use JMS to push the data to the other applications.  The proof of concept
                is still in the works; if it doesn't work out we'll probably use straight SQL, through a database link.  We decided to go with JMS to achieve a lower level of coupling between the applications.

                >Its good to hear that you've had success with this type of architecture.

                Tim Boettcher

                boettt@...




                [Non-text portions of this message have been removed]
              Your message has been successfully submitted and would be delivered to recipients shortly.