I have, as a task, designing a fairly rich DBMS schema for some future systems. I’m not keen on doing a whole lot of by-hand reconciliation, so am taking a somewhat different approach. I am analytically describing the relationships between tables, and then generating SQL DDL from the results.
I am doing some of the intermediary work in Lisp; probably a little bit dubious, but the translation is making sure that everything that I do have is pretty strictly defined. I created Lisp “primitive” functions such as “make-table”, “add-column” which do a little bit of data validation, and do any SQL generation in one spot. The more interesting part is then to characterize the creation of various sorts of tables:
- first-class-object indicates a table that sits on its own.
- make-subobject indicates a table that depends on one of the “first class” objects.
- make-association-table indicates a table that links two “first class” objects together.
- make-transaction-stream indicates a table that contains transactions/activities
- make-queue sets up a table that will contain work that is to be processed (and, presumably, cleared out upon completion)
The more structure that may be characterized in a fashion that makes it amenable to automation, the better 🙂