Generating test data for databases

Possibly a useful tool.

How Craig Kerstiens writes SQL

I guess I was on a modeling tear in May (I can hardly even remember May at this point; it's been a busy year).  Anyway, this is a nice little guide about how one PostgreSQL expert thinks about carefully constructed SQL.

DIP in the wild

So here's a really well-written article about software engineering - which paradoxically, given that I earned my bread and butter with software development for over a decade and have both a BS and and MS in CompSci, I really know very little about.

But there is a lot of good thought out there (in the Java enterprise world, mostly) about how to engineer this kind of software and model at scale. It's important stuff; out on the enterprise edge people are pushing the boundaries of the number of details a human organization can actually keep track of, and the way they do it is quite instructive in terms of how we think about software.

And this author just plain writes well.

Filed under data modeling because I don't even have a thread for software engineering, it's so far from my normal stomping grounds.

Data modeling and semantics

I've been musing more about the semantics of data modeling lately - or really rather about the fact that data modeling is a form of semantic manipulation.  The thing that makes semantics interesting is how semantic structures can be mapped onto other semantic structures. That is, the mapping, or recognition, of structures is really what semantics buys us.

In the case of accounting (sorry, I do tend to fixate on particular applications for months or years at a time), it would be instructive to gather the various data models used in open-source software (well, and open formats such as QIF used for non-open software) and do a kind of line-by-line comparison. A mapping, in fact - a mapping onto the semantic constructs that accountants use to talk about accounting.

That nexus is where semantic programming resides, in potential anyway.

At any rate, comparison of projects in this manner would allow us to identify certain features of accounting data structures that were incorporated into or absent from different models.  Description of those variants is also part of modeling, and a full description would permit us to auto-generate data migration tools.

And once you can migrate data back and forth between different representations, well, then you have semantic data management, I guess.  Not (semantic data) management, that is, but semantic (data management).  You've started to graduate from data to knowledge.

Accounting and data management

So I've been looking at accounting in more detail lately (it's coming up on tax season, and then there's data modeling, and business plans) and I've been having some various epiphanies.

I've always thought of accounting as being primarily a database application.  Which it is, naturally, but I've come to realize that the purpose of accounting isn't actually data management - the purpose of accounting is to predict the future.  Well - and satisfy the tax authorities and make sure your customers don't forget to pay you, but one of the main reasons you do accounting is so you can plan.

In looking for data models for accounting, I first looked at the mother of all data model sites for a basic model.  Data models are fluid (they don't get treated as very fluid, but at the semantic level, they should be seen as fluid).  In other words, there are a lot of different ways to model accounting data.  There are standards, of course, some of which are mandated by governments so that corporate reports and statements take a standard form, and some of which are just good ideas - but they only make sense if you accept that the purpose of accounting is to tell a story about a company and explain how that history allows you to make such-and-so a prediction about next year.

Case in point: the chart of accounts.  The chart of accounts is the list of all the separate accounts that a given accounting system tracks for a company.  By convention, they're numbered, and the first digits of the numbers have meaning. ('1' being assets, '2' liabilities, and so on.) The reason for this numbering system may not be obvious to the programmer - but account numbers must often be written down on papers or whatever, and if they have internal structure it's easier to see what's what on these paper documents.  In other words, the numbering system is an interface to traditional document management and is justified.

So here's what I learned about planning a chart of accounts.  If you initially use a very simple arrangement, but then as the company grows you start ramifying into separate accounts for separate purposes, then as the page I linked to above notes (and I highly recommend that entire site, actually - very information-rich!), you lose the ability to compare year to year. Because the point of accounting is to compare year to year!  (And also to make sure you get paid for invoices, and you pay your own incoming invoices, etc.)

Which brings me to data management.  There's a concept of master data management (MDM, which to me means the Manufacturing Data Management system I worked on at Eli Lilly in the 90's, but that's another story entirely) which can be seen as version control for data that warrants it.  Master data tends to be complex, slow-changing, benefits from versioning, and is largely global (although for reasons of performance it can be mirrored here and there).  The processes of master data management can be seen as relatively independent of the processes that simply use the master data for other purposes (which are transactional processes).

Now clearly, master data management and data model management are essentially the same thing: they involve definition of the semantics of a given company.  They can evolve over time, but if they do, you need to keep track of how they've done so.  For example, our list of customers naturally changes over time; a properly versioned customer master can tell us when a customer was a valid customer, when they stopped being a customer, and so on, and the customer master at a given point in time can be seen as a snapshot of that process.  The same can be said of the data model; as our data management needs grow, we start to make distinctions that were unimportant before - perhaps we have different processes for retail customers on our website and larger contracted customers, and so having a single record that addresses both sets of needs may be too complex.  This is an area of data management that seems to be really poorly considered and addressed, but maybe I'm just too naive at this point.

Anyway, back to accounting.  In terms of the chart of accounts, you can easily see that accounts fall into a hierarchy that can ramify to an arbitrary extent - but as they ramify, if you want to preserve comparability, you need to "back-categorize" existing entries to fall into one of the new subcategories.  This is arguably what that post from last week (or last month, time flies) is doing using machine learning; taking the posts from Chase as a sort of general ledger, it categorizes them into subcategories using a machine-learning algorithm I haven't examined in any detail.  The same kind of thing could be done if we just split a general asset ledger into a petty cash and bank account setup, for example.

If we look at the overall process of accounting, the "accounting cycle", we see that there are actually two phases involved.  The first phase is really not even a phase - it's an ongoing thing.  As each transaction happens (an invoice is received, money changes hands, etc.), it's identified and a determination is made of its significance to the accounting system.  That is, if we receive money, we determine which account should be credited, why we got it (which invoice the customer is paying), and so on.

Then, periodically, we close the books - we reconcile all the outstanding weirdnesses, fix things up with corrections if necessary, and issue statements that can be given to shareholders, governments, and management planners to explain what happened to the company and what can be expected to happen next period or next year.

That's what I learned about accounting in my browsing today.  There are a couple of side points on data management I'd like to address as well.

First is business rules.  As noted in the data model tutorial here, business rules are generally implemented as constraints on the database that prevent certain nonsensical things from happening - an order for a non-existent product, for example.

Second, a canonical data model ([here] and [here]), a popular concept lately due to service-oriented architecture, can be seen as a lingua franca between two specific data models. We can define transformations between each specific model to the canonical model to permit communication between the specific systems.

Third, a link to Microsoft's modeling tool, such as it is, and the observation that my own notion of modular data modeling really seems underrepresented out there still, and maybe there's a need for it.

Actually, for basic accounting concepts the GnuCash manual is pretty fantastic.

Data model XML

The Data Mining Group has published an XML standard "PMML" for the interchange of data models.

You know, I really need some kind of better semantic model for XML.  For myself, actually, but at the language level as well.  There's got to be a good way to think about XML as it relates to data structures or APIs or something, but somehow when I look at an XML specification I feel dry.

edn: extensible data notation

Interesting.

Data models II

Oh em gee - poking around further at that list of data modeling tools gives me this lovely, lovely treasure trove of prepackaged data models!

Data modeling

So I've decided to do more coding, now that our lives are rearranged in Europe (see, we're renting a house that doesn't need renovation, and there's public transit so I don't have to play taxi driver for the whole family every freaking day, and so on).  My first coding target is to revamp my job management system for the translation business.

Here's the problem whenever I start to code anything: I always want to develop the tools to develop the tools to ... infinite regress.  It's a bad, bad habit.  But what's got me going today is that, since I had to move my database from Access into SQLite (long story, but: my Access is 32-bit and I'm now running a 64-bit Perl; you cannot get from one to the other.  At all.  Microsoft wants me to upgrade to a 64-bit Access and I ain't gonna do that) - anyway, I touched SQL and now I'm well into fiddling with database design.

Which brings me to data modeling tools.  In Perl.  And here - just like in every other thing I dabble in - I find that Dave Freaking Rolsky is ahead of me.  Twice.  Once with Alzabo (when asked, he tells me Alzabo is a doornail, a dodo, its metabolic processes have stopped), then rewriting it entirely in Fey (which is just still too Perly for me to use without distraction).  But, like all things Rolsky, he seems to be off doing fantastic things which are not the things I initially wanted to do.  Alzabo did indeed include at least a rudimentary data modeling tool, but Fey is just an ORM.  A really fine ORM, it is true, but that's not (all of) what I'm after.

No, I want a database-agnostic way to model data, play with it, move it around, restructure it.  Then, once I really like it, then I want a Perl ORM, and one I can use without all that { this => 'that' } in it (in short, of course, Decl).

So back to the Goog I go, this time dropping my insistence on Perl, hoping at least to find some inspiration and maybe a tool I can use without any time investment at all.  I find this compendium, which at least manages to convey to me that there is a whole lot of money in data modeling.  But I knew that.  And it does give me a helpful link to SQLDeveloper, which might be kinda-sorta what I'm looking for, at least to get off the ground.

(Update: nope, it still isn't what I'm looking for.)  (But here's the Wikipedia page on data modeling, which is a pretty cool page.)

Along the way, it also shows me the fascinating Reverse Snowflake Join diagramming tool, which when given a complex SQL statement can discombobulate it into a diagram, and a site filled with interview questions, including Perl data modeling questions.  Yes, it's a whole site devoted to kata.

So that's my brain dump for the day.  Thanks to my foray into online AI classes, followed by a spring chock-full of travel, it's been about eight months since I did any serious coding.  I can feel the brain cells dying.  Maybe there's still hope, though.  Wish me luck, hypothetical reader.