[OPEN-ILS-DEV] DB schema documentation

[Mike Rylander]

On Thu, May 29, 2008 at 5:19 PM, Josh Stompro <stomproj at larl.org> wrote:
> I'm trying to understand the database design, and I find that I get hung up
> on figuring out how exactly certain columns are used or how tables are
> related.  Finding where it is used in the source and understanding what is
> going on is time consuming when I just want general info.
>

The most direct description is probably embodied in the fm_IDL.xml
file.  When I need to reference something quickly that's generally
where I look first.

> There is documentation scattered around in the wiki about different sections
> of the database, but I don't think they are always up to date or complete.
>  The ERD that is listed in the wiki
> (http://open-ils.org/documentation/evergreen_1.1.3_erd.html) seems to be
> more of a pretty print of the schema and not what I would consider an entity
> relationship diagram since there are no diagrams.

There are internal anchor links for defined fkeys, but, yeah ... no
diagrams.  I have generated some dia diagrams in the past, and
attempted generating UML, but there are some parts of the schema
(particularly in the money schema) that are not modeled well by those
attempts.  pg_autodoc was used to generate that html as well as the
dia and UML (which aren't posted).

> How do the core devs feel about commenting up the sql files?  It wouldn't
> look as pretty but it would be a one stop place for questions about how the
> database is designed for those that have a question about what column X is
> for.
> Would a patch that did something like this be accepted/welcomed?
>

It would be accepted with open arms!  There is some of that in there
now, but not enough, and much of it is out of date (and not
appropriate for in-db comments, really).  If you have any questions
about specific column use then please fire away at this list.

> ---040.schema.asset.sql---
>
> DROP SCHEMA asset CASCADE;
>
> BEGIN;
>
> CREATE SCHEMA asset;
>
> CREATE TABLE asset.copy_location (
>   id        SERIAL    PRIMARY KEY,  -- Primary key for asset.copy_location
>   name        TEXT    NOT NULL,  -- Name of the copy location
>   owning_lib    INT    NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE
> INITIALLY DEFERRED,  -- Links a copy location to an organizational unit
>   holdable    BOOL    NOT NULL DEFAULT TRUE,      -- Specifies if the
> location contains items that are holdable/requestable
>   opac_visible    BOOL    NOT NULL DEFAULT TRUE,  -- Specifies if the
> location will be visible in the opac
>   circulate    BOOL    NOT NULL DEFAULT TRUE      -- Specifies if the
> location contains items that will be allowed to circulate
> );
>
> COMMENT ON TABLE asset.copy_location IS "Represents a collection of items.
>  Sometimes referred to as the shelving location.  copy_location has a Many
> to one relationship with actor.org_unit and a one to many relationship with
> asset.copy";
> COMMENT ON COLUMN asset.copy_location.id IS "Primary key for
> asset.copy_location";
> COMMENT ON COLUMN asset.copy_location.name IS "Name of the copy location";
> ...
> ...
>
> CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted
> IS FALSE;
>
> COMMENT ON INDEX copy_barcode_key is "Creates a unique index for the barcode
> field in asset.copy.  Prevents duplicate barcodes from being entered into
> the database";
>

(Just a note, this is to protect against duplicate non-deleted copies.
 Barcodes from deleted copies can be reused, though this restriction
may become stricter at some point in the future as the benefit is
weighed against the potential confusion.)

-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone: 1-877-OPEN-ILS (673-6457)
 | email: miker at esilibrary.com
 | web: http://www.esilibrary.com