6 Replies Latest reply: Mar 19, 2010 4:21 PM by jduprez RSS

    [Java and all else] How do you document DB design?

    jduprez
      Hello,

      Although most of the technical choices are explicit in SQL (UniqueKey & ForeignKey constraints, indexes,...), the semantics of the data columns and their constraints seem better served as plain human-readable comments ("ID column: identifies the plane copy; 6 figures are enough as we don't expect to sell more than 1 million planes in a foreseeable future...").

      In my current system, (EJB-based, but the DB schema is not created by the JPA-compliant ORM, but via SQL scripts), I see the following ways to write and maintain this documentation:
      - SQL comments in the schema creation and patch scripts
      - Javadoc comments in the Java source of the Entity classes.
      - UML notes in UML diagrams(1)
      - external "Architecture and design" document.

      All 4 of them are used, sometimes inconsistently, for various parts of the design choices; I mean, the docs do not contradict themselves (not yet, but it's a mere question of time), but some tables are commented in SQL scripts, others are in design docs, and the details for some columns are in entity javadocs. Although each developer may find it handier to "write" the doc via his preferred medium, it becomes increasingly difficult to "maintain" theoverall documentation.

      Our current situation is that most of the project team members are Java developers, so it would probably be better accepted and served if we standardized that we document DB tables and columns in Java source, but I am worried that:
      - someone else pouring over our DB (ex: DBA helping us to optimize things, or other team developping a data-mining tool to leverage the historical data in DB) may not be as comfortable with Java
      - this may not cover all design choices of the DB schema:
      - - - - first, although that is the case currently, in the future there might not be a 100% 1-1 mapping between e.g. each entity class and a DB table. Maybe some columns will not need to be mapped anymore,...
      - - - - second, I fear some DB constructs are not amenable to Java counterparts; no accurate idea there, I'm not an SQL nor JPA expert, but I presume Indexes, table partitioning, tablespaces, for example (yes, the DBMS is Oracle :o) are not taken into account in JPA mapping.

      And if we document those choices at the SQL level (my preferred idea so far), this will gradually make the javadocs in the entities obsolete, or removed altogether, and that will make future maintenance of the Java source risky.

      How do you document your DB design in general?
      Do you have specific advices for my case?

      Thanks in advance,

      J.

      (1) Just for the record, here are a few posts that refer to modelling the DB in UML:
      [A post on this forum highlighting that UML 1.4 standardizes a notation for RDB modelling|http://forums.sun.com/thread.jspa?messageID=1383724#1383724]
      [A DB modelling tutorial|http://www.tomjewett.com/dbdesign/dbdesign.php] (not sure whether it leverages the standard mentioned above :o)


      P.S.: "Java and all else" as in, damn, I'd love to use the familiar JDC forums and people to discuss not only Java-related issues, but also [all other things that surround Java|http://forums.sun.com/thread.jspa?threadID=5422264&tstart=0] (other technologies, processes, people) and enable to make workable systems out of Java code.
        • 1. Re: [Java and all else] How do you document DB design?
          jschellSomeoneStoleMyAlias
          jduprez wrote:
          All 4 of them are used, sometimes inconsistently, for various parts of the design choices; I mean, the docs do not contradict themselves (not yet, but it's a mere question of time), but some tables are commented in SQL scripts, others are in design docs, and the details for some columns are in entity javadocs. Although each developer may find it handier to "write" the doc via his preferred medium, it becomes increasingly difficult to "maintain" theoverall documentation.
          I doubt you can fully document schema design decisions using only code comments. And isn't just that someone doesn't know java but also that them must know where to look to find the java code (or javadocs.)
          Our current situation is that most of the project team members are Java developers, so it would probably be better accepted and served if we standardized that we document DB tables and columns in Java source, but I am worried that:
          - someone else pouring over our DB (ex: DBA helping us to optimize things, or other team developping a data-mining tool to leverage the historical data in DB) may not be as comfortable with Java
          - this may not cover all design choices of the DB schema:
          Really likely.
          How do you document your DB design in general?
          Ideally...

          1. One or more actual design documents that are kept in one place in source control. They do not need to detail every detail. For example it wouldn't be necessary to document every CRUD proc but could be sufficient to mention that all tables (unless doc'd otherwise) have CRUD procs. Another example might document a customer and order table but not document the exact details of how the relationship is implemented.
          2. Table, and proc dictionary maintained as one or more text files. Those files and schema are all in source control. Then a tool that takes the schema and text files and produces documentation and flags missing values as part of the process. Last time I did this the documentation was a html output. That particular one supported easily mapped foreign keys as links and some custom tagged text to document harder relationships. Procs were also part of that. At the top was a link to "procs" or "tables" with successive drill downs.
          • 2. Re: [Java and all else] How do you document DB design?
            jduprez
            Thank you for your interest and advices.
            [it] isn't just that someone doesn't know java but also that them must know where to look to find the java code (or javadocs.)
            Right.
            Ideally...
            1. One or more actual design documents that are kept in one place in source control. They do not need to detail every detail. For example it wouldn't be necessary to document every CRUD proc but could be sufficient to mention that all tables (unless doc'd otherwise) have CRUD procs. Another example might document a customer and order table but not document the exact details of how the relationship is implemented.
            Much the same way an architecture document describes the overall design of the application, without drilling the details of each class' design (indeed without even mentioning most of the classes).
            I was hoping I could avoid maintaining a detailed document separate from the sources (either Java or SQL sources), to avoid duplication (duplication=mismatch one day or the other).
            2. Table, and proc dictionary maintained as one or more text files. Those files and schema are all in source control.
            Do you mean, a proprietary text format (a la tabledesign.txt), or +.sql+ source files? Again, you seem to suggest the scehma info is present in two locations.
            Then a tool that takes the schema and text files and produces documentation and flags missing values as part of the process. Last time I did this the documentation was a html output. That particular one supported easily mapped foreign keys as links and some custom tagged text to document harder relationships. Procs were also part of that. At the top was a link to "procs" or "tables" with successive drill downs.
            That sounds very interesting, and would nicely replace some of the graffitis on our openspace :o)
            Did you use an in-house tool, or something on the market?
            I just googled for such a tool, and found mostly tools specific to MS SQLServer, or generic content-to-html exporters, but no oracle-schema-to-html exporters.
            Such a tool looks like an interesting internship subject, but I'd be surprised if nothing at least embryonary existed, even in the open-soruce world. I am probably a poor googler!
            • 3. Re: [Java and all else] How do you document DB design?
              jschellSomeoneStoleMyAlias
              jduprez wrote:
              Much the same way an architecture document describes the overall design of the application, without drilling the details of each class' design (indeed without even mentioning most of the classes).
              I was hoping I could avoid maintaining a detailed document separate from the sources (either Java or SQL sources), to avoid duplication (duplication=mismatch one day or the other).
              On larger projects I don't believe there is any choice. At my current company we have one database that spans multiple deliverables.
              2. Table, and proc dictionary maintained as one or more text files. Those files and schema are all in source control.
              Do you mean, a proprietary text format (a la tabledesign.txt), or +.sql+ source files? Again, you seem to suggest the scehma info is present in two locations.
              I didn't say two locations unless you meant files. Then yes.
              The schema of course must be a SQL anyways.
              You can of course keep the addtional info as special comments in the SQL, but when I did this, and even in retrospect, it seems better and perhaps easier to keep them seperate. I have done special comments in SQL before and it seems a bit of a kluge but you do have the single source. But in that case I was the only one maintaining it too.
              Then a tool that takes the schema and text files and produces documentation and flags missing values as part of the process. Last time I did this the documentation was a html output. That particular one supported easily mapped foreign keys as links and some custom tagged text to document harder relationships. Procs were also part of that. At the top was a link to "procs" or "tables" with successive drill downs.
              That sounds very interesting, and would nicely replace some of the graffitis on our openspace :o)
              Did you use an in-house tool, or something on the market?
              In house.
              • 4. Re: [Java and all else] How do you document DB design?
                jduprez
                Thank you again.
                A couple more questions:
                2. Table, and proc dictionary maintained as one or more text files. Those files and schema are all in source control.
                Do you mean, a proprietary text format (a la tabledesign.txt), or +.sql+ source files? Again, you seem to suggest the scehma info is present in two locations.
                I didn't say two locations unless you meant files. Then yes.
                Yes I meant two files. I trust the team to get the doc file lagging a few revisions behind the SQL file, and to correct that I would have to include one more step in the review process (e.g. "review CM actions to check that the doc file is updated consistently with the SQL source").
                You can of course keep the addtional info as special comments in the SQL, but when I did this, and even in retrospect, it seems better and perhaps easier to keep them seperate. I have done special comments in SQL before and it seems a bit of a kluge but you do have the single source. But in that case I was the only one maintaining it too.
                What do you mean by "special"?
                If we don't try to generate a browseable HTML out of the schema (although the idea is appealing, I don't have the resource to make such a tool), do you see anything kludgy in maintaining SQL comments interpersed within DDL code?

                My team is small, and when I mentioned external players it was in a read-only mode (audit, expert review,...), so I'd incline for the "comments in SQL", in addition to a high-level DB architecture doc.

                Best regards.
                • 5. Re: [Java and all else] How do you document DB design?
                  jschellSomeoneStoleMyAlias
                  jduprez wrote:
                  Thank you again.
                  A couple more questions:
                  2. Table, and proc dictionary maintained as one or more text files. Those files and schema are all in source control.
                  Do you mean, a proprietary text format (a la tabledesign.txt), or +.sql+ source files? Again, you seem to suggest the scehma info is present in two locations.
                  I didn't say two locations unless you meant files. Then yes.
                  Yes I meant two files. I trust the team to get the doc file lagging a few revisions behind the SQL file, and to correct that I would have to include one more step in the review process (e.g. "review CM actions to check that the doc file is updated consistently with the SQL source").
                  The tool I wrote would throw errors if the comment file didn't match the schema. And if comments were not provided. That of course doesn't stop someone from documenting a table with "a table".
                  You can of course keep the addtional info as special comments in the SQL, but when I did this, and even in retrospect, it seems better and perhaps easier to keep them seperate. I have done special comments in SQL before and it seems a bit of a kluge but you do have the single source. But in that case I was the only one maintaining it too.
                  What do you mean by "special"?
                  For code generation I have a schema file with the following (pseudo sql.)
                      create mytable
                      (
                         mytable_id int,
                         column_foo varchar(10);
                         column_fum varchar(20)
                          -- Query: for_a_query (column_foo, column_fum)
                      )
                  The generator consumes the schema and generates the standard CRUD which would include a query based on the primary key.
                  The above comment is used to provide an additional query where the proc is named 'for_a_query' (munged with table name) and takes two parameters (column_foo, column_fum) whose type/name matches those of the table.
                  If we don't try to generate a browseable HTML out of the schema (although the idea is appealing, I don't have the resource to make such a tool), do you see anything kludgy in maintaining SQL comments interpersed within DDL code?
                  Of course that is doable.
                  • 6. Re: [Java and all else] How do you document DB design?
                    jduprez
                    Thank you for these clarifications.