Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Angry database doccumentation

    what are essntial steps in doccumenting a database other then following'
    for example
    External Schema
    Conceptual data model
    People Involved
    Approaches for Database Development
    Software architecture document
    Information Systems Architecture
    Identify Corporate Planning Objects
    System requirement specification
    erd
    dfd

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    There are no essential steps for documenting a database. Most of the databases that I'm asked to fix have worked for months or years without any documentation at all.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Almost all our customers are Australian banks and financial institutions, and they have both an IT Audit, as well as a "power" user requirement; the latter demand to use the database directly, from various (simple to complex) reporting tools.

    When I deliver databases (fixed price, fixed delivery), it must have a fully detailed Data Model, that's one step deeper than the ERD. Full technical (not laymans) commentary at the Entity and relation level is mandatory. If you use good naming standards, then commentary at the column level is highly desirable but not mandatory. Most modern data modelling tools (ERWin, PowerDesigner, ERStudio) provide this capability within the tool itself. The final hardcopy consists of a few chapters on approach, design requirements, standards, etc; and the appendices are produced directly from the modelling tool. Plus a few scripts.

    For developers (working with me on the project long before the final documentation is available, actively developing against the database, which I am continually extending), the current version of the model, included the said commentary, in hardcopy and electronic format, is adequate. This assumes some stability, predictability (no sudden undiscussed changes), and of course coding standards, so that their code is isolated from the effects of change. During an active database/app development project I usually have one two-hour meeting per week, plus I make myself available to answer problems only. This is possible precisely because the data model is documented, and standards are in place.

    To give you context: just as I am working on the data model, and it is changing within the identified project stages, they are working on the DFDs, and changing that. I am dependent on them for that. Sometimes I supply that as well, in which case the DFDs have to be supplied along with a matching Data Model, at defined project timeframes. They go hand-in-hand; they have to match.

    I always provide an Architecture doc. The rest of the items you list as non-essential. No one produces a System Spec any more; unless the project is sent offshore, then it is considered mandatory. I disagree, my experience is the Definition document and the fully defined Data Model is enough.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Hey derek,

    I don't understand why you are not freezing the specs
    actively developing against the database, which I am continually extending
    Does it not make the programmers have a rough time?

    An approach that could be followed is to design and define the basic entities and attributes in full. Freeze these structures. Programmers then would have to define only temporary tables etc.

    End

  5. #5
    Join Date
    Oct 2009
    Posts
    6
    thanks for kind reply sir actually my question was that which reports are used to document a database
    e.g capacity planing
    data resource palaning etc

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    There are no essential steps for documenting a database. Most of the databases that I'm asked to fix have worked for months or years without any documentation at all.

    -PatP

    You funny

    (But true dat)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by AnanthaP
    Hey derek,
    I don't understand why you are not freezing the specs
    Change is good. A business that isn't changing is dying. Naturally your software and data solutions should reflect that change.

    Does it not make the programmers have a rough time?
    Not if your programmers embrace an iterative and Agile approach.

  8. #8
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Quote Originally Posted by AnanthaP View Post

    1 I don't understand why you are not freezing the specs

    2 Does it not make the programmers have a rough time?

    3 Programmers then would have to define only temporary tables etc.
    1 I did not mean it like that. Yes, of course, for any single deliverable, a release, the specs are frozen. But the spec you start the project with ... and the spec that is the final documentation of the release, are two different things. The project handles all issues that were not complete in the initial definition; resolves all conflicts and bugs; there is more precision is the final doc.

    Second (read my para in context), during the project, when you are working closely with a team of developers, the data model and the data dictionary are in a state of change. Not change as in change of scope of functionality, but change in terms of deliverable between me and the developers. I do not deliver the entire complete data model and dictionary at the beginning; just the initial data model (no dictionary). I deliver it in stages, to match the functions they are delivering. Each minor release corrects any issues in previous releases and provides complete new tables and columns for the new functions. That progression, and state of reasonable flux within the scope of the release, is normal in a development project. At the end, I deliver the final data model and the full dictionary.

    (Really, the point I was making in the original post was that developers do not need a final, complete data dictionary [the users and auditors do]; they need an electronic working [known not to be final] model.)

    2 The reason programmers do not find it rough is, the strict use of coding standards, which isolates their code from the effects of the database structure changing. Iterative or progressive development is normal in this industry/class of servers/value of project. It is far removed from what they do on MS systems.

    3 Programmers are not allowed to define temporary tables. The overuse of temp tables is a classic hallmark of (a) unnormalised database [therefore it would be my fault, and I had better fix it in the db, so that the programmer does not have to use a temp table] or (b) programmers who cannot code SQL for a normalised db [we help them find employment elsewhere]. On my projects, they are not allowed to create #tables, they have to get me to do it; this is a policing action to prevent the misuse of tempdb.

    3.1 For those very few temp tables that are valid, in addition to making sure the data modeller does it, and does it correctly, I have methods that ensure they are high performance, and thus do not have the common problematic impact on the server.
    Last edited by Derek Asirvadem; 11-06-09 at 22:19.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  9. #9
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Derek,

    The context is clear. Now some clarifications.

    (1) What do people normally do on MS systems?
    (2) In my case also, progressive developments are normal and additionally since I work in a large tier II automobile company, I have to face the end user and thus have to give effective systems from day zero. The trick is of course to minimise scope-creep (in a release cycle) by doing a good system study. Regarding my post about freezing the specs, I was going by your earlier post where you said
    actively developing against the database, which I am continually extending),
    (emphasis is mine).

    Hope this clarifies the point.

    End

  10. #10
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    naumanarshadkiyan

    Sorry, I missed your post.
    thanks for kind reply sir
    My pleasure.
    actually my question was that which reports are used to document a database. e.g capacity planing; data resource planning etc
    Those are operational reports, not really db documentation. At the outset, we give them a clear idea of the requirement (Estimate), usually that is used purchase the machine, and allocate SAN resources. It is part of the Definition doc. Somewhere in the early stages of the full development, after some data loading has been done, those figures are formally Confirmed. At Delivery, those figures are Confirmed Delivered; it is part of the inventory of components Delivered.

    We have scripts that produce all database metrics used for capacity planning and resource planning; they work directly from the database catalogue (works anywhere, on what is really in the db (not what we think is in the db). You can request any level you want; table; index; partition; segment; device; etc as well as size; performance; activity; fragmentation characteristics. Point is, you want it all in the onereport but you want to be able to specify the columns. It takes seconds to run. These are handed over to Operations, and they run them (along with update stats, db consistency checks, backups, etc), every week or whatever, from crontab entries or ad hoc.

    I use these same scripts as the first step in either a Technical Audit or Performance Tuning assignment. It gives me a complete physical inventory of objects. They are not a substitute for GUI based Administration tools, but they are the batch or character/text equivalent. The reports from the scripts end up as Appendices (before and after snapshots) in the TA or PT report. They are also extremely important as an Audit List before and after a change (either a minor db/app release or a PT reconfiguration).

    Here's a few to give you a flavour:
    Database-Device Allocation
    Segment with Activity columns
    Index with Performance columns (Be sure to check p32 for the summary and errors)
    Partition with Fragmentation & Stats columns
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Derek Asirvadem View Post
    AnanthaP
    I do occasionally get dragged across to the MS side to inject some quality into that area which is usually devoid of it.

    I have implemented a few small systems on MS as well. But I am in no way a specialist in that end of town, in fact I do not see the point in being a specialist in mere low-end "software" that changes completely every few years, demanding a re-implementation.
    Really? Cuz, I dropped out of Sybase seven years ago after attending a conference in Florida where it became abundantly clear to me that it is a dead and stagnant platform. I suppose if you never want to learn anything new and more powerful, or are incapable of adapting to change, then legacy systems such as sybase are the best career choice for you.

    Quote Originally Posted by Derek Asirvadem View Post
    AnanthaP
    But I do know it is full of people who have no formal IT education, and are clueless re standards (they think the single-vendor offering is the "standard"), producing linked spreadsheets which are presented as "databases". The predictable result is they get replaced and "refactored" frequently; so it doesn't matter what they do on the MS side, because it has no life span of relevance.
    Do tell?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Anathap
    Quote Originally Posted by AnanthaP View Post
    (1) What do people normally do on MS systems?
    (2) effective systems from day zero ... The trick is of course to minimise scope-creep ...
    Actually, I answered both your questions in fair detail. But the post has been deleted by an unknown person for unknown reasons. It not only stated the answer to (1), but explained why that segment of the market is what it is, and why people do what they "normally" do there. I can only speculate that someone took my considered and experienced technical analysis of the MS segment personally (even though there was no attack of any kind, and certainly no personal attack). The post was not "bait" or "trolling" (I do not engage in such behaviour, but I cannot speak for other peoples interpretation of my posts), it was merely a deep and honest statement of the realities that have to be dealt with commercially, which I understood to be your concern.

    I dare not post again, since they will merely identify it as justification to ban me.

    I cannot imagine what was wrong with my detailed response to (2).

    Therefore, in the meantime, feel free to email me from my profile.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  13. #13
    Join Date
    Jan 2008
    Posts
    11
    Quote Originally Posted by blindman View Post
    Really? Cuz, I dropped out of Sybase seven years ago after attending a conference in Florida where it became abundantly clear to me that it is a dead and stagnant platform. I suppose if you never want to learn anything new and more powerful, or are incapable of adapting to change, then legacy systems such as sybase are the best career choice for you.


    Do tell?
    Blindman,

    This thread is quite revealing, clearly you are not as blind as some others are. I was wondering why the question I posted got such a scathing response encouraging me to rebuild my entire system lest I kill people. Now I see I too was blinded by the Microsoft ineffectiveness. I guess having sub-second response times from my lousy old Microsoft SQL Server 2000 data warehouse that has 20 billion rows in several facts isn't up to the standards of some folks. Funny how that just seems to be a spreadsheet.

    It amazes me how someone can be so authoritative in judging a product to be inadequate and yet have little to no experience using it. If all Mr. D saw of MS SQL was an Excel front end, he clearly didn't see any adequate usage. But then again, MS SQL is also not priced to compete with German Luxury cars either.

  14. #14
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Anyway, to get back to the original query.

    As systems get more complex and encompass more and more features, they tend to interact more and more with other systems. In this context, I would add an overall "context" diagram that tells the interaction between the system on hand and other external systems. Here I would detail the nature of the links, whether internal or external, volumes etc. Tis would carried overinto specific FRS.

    To take an example from well known financial systems, it could be links to and from payment gateways, stock quotes, queries of retail customers, corporate bulk postings, statutory and regulatoy information, audit runs etc.

    End

  15. #15
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    AnanthaP

    Yes. When you have system interaction you need a context or interaction diagram.

    For external systems (payment gateways; SWIFT/FIX; etc) we normally have the gateway fully defined once, that includes the interaction/protocol requirements, once. Then each consumer system needs just a chapter on the context/interaction it has with the external system/gateway.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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