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.

Tables
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