Database Design

by Dan Connolly (with John Matt), Aug 2004
$Id: bottlesDB.html 36 2004-09-02 04:31:33Z connolly $

Database Schema

This table is used to generate bottlesDB.sql (see details below). The test data set follows this schema.

Table Field Datatype ref Description
Patients id int
surname text
givenname text
born date
email text hmm... perhaps something more constrained that text?
phone text hmm... perhaps something more constrained that text?
chart text use patient chart identifier as key? or use integer key as patient chart identifier?
Instructions id int
who int Person
what int Supplement
rate int Rate
start date
stop date
Clinics id int
name text
address text one or two lines
city text
st text 2 letter state abbreviation
zip text NNNNN(-NNNN)
phone text
Supplements id int
desc text
Products id int
what int Supplements at most one product per supplement, for now.
qty int number of pills in this type of bottle
price float hmm... decimal?
Rates id int
label text e.g. "2 pills, 3x/day"
num int numerator of pills/day (e.g. 6)
denom int denomination of pills/day (e.g. 1)
Counts product int Products
qty int
minimum int low water mark
Orders id int
who int Persons
product int Products
qty int
delivery date
Items id int
product int Products
qty int

Per twisted conventions, Database tables will be named with plural nouns.

Generating SQL and OWL from HTML Tables

The schema is generated from the above table by some rules in the Makefile:

bottlesDB.sql: bottlesDB.owl owl2sql.xsl
        $(XSLTPROC) -o $@ owl2sql.xsl bottlesDB.owl

bottlesDB.owl: bottles.html grokDBSchema.xsl
        $(XSLTPROC) -o $@ grokDBSchema.xsl bottles.htmlxsl