Designing SQL Schemas in Lisp

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 🙂