[OPEN-ILS-DEV] DB schema documentation

[Josh Stompro]

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.

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.  

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?

---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";

---

Thanks
Josh

-- 
Lake Agassiz Regional Library - Moorhead MN larl.org
Josh Stompro               | Office 218.233.3757 EXT-139
LARL Network Administrator | Cell 218.790.2110