Re: Plain SQL representation(s) of SKOS

Hi Dan:

FWIW, the software running at id.loc.gov uses a MySQL relational
database for persisting the Library of Congress Subject Headings in a
SKOS-like schema. The schema is largely driven by the Django ORM, but
here it is if you are interested. If you'd like to see the Django
models I could send you those too:

CREATE TABLE "authorities_concept" (
    "lccn" varchar(25) NOT NULL PRIMARY KEY,
    "pref_label" varchar(250) NOT NULL,
    "prefix" varchar(2) NOT NULL,
    "created" date,
    "modified" datetime,
    "lcc" varchar(50),
    "heading_tag" varchar(3) NOT NULL
)
;
CREATE TABLE "authorities_alternatelabel" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" varchar(300) NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_editorialnote" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_scopenote" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_changenote" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_historynote" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_definition" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_example" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_source" (
    "id" integer NOT NULL PRIMARY KEY,
    "text" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_closematch" (
    "id" integer NOT NULL PRIMARY KEY,
    "url" text NOT NULL,
    "concept_id" varchar(25) NOT NULL REFERENCES "authorities_concept" ("lccn")
)
;
CREATE TABLE "authorities_concept_related" (
    "id" integer NOT NULL PRIMARY KEY,
    "from_concept_id" varchar(25) NOT NULL REFERENCES
"authorities_concept" ("lccn"),
    "to_concept_id" varchar(25) NOT NULL REFERENCES
"authorities_concept" ("lccn"),
    UNIQUE ("from_concept_id", "to_concept_id")
)
;
CREATE TABLE "authorities_concept_broader" (
    "id" integer NOT NULL PRIMARY KEY,
    "from_concept_id" varchar(25) NOT NULL REFERENCES
"authorities_concept" ("lccn"),
    "to_concept_id" varchar(25) NOT NULL REFERENCES
"authorities_concept" ("lccn"),
    UNIQUE ("from_concept_id", "to_concept_id")
)
;

Since the id.loc.gov service is going to eventually contain things
that don't naturally fit into the SKOS model we are likely going to be
moving to a more flexible non-RDBMS solution in the coming year. But
for now this simple approach has served us quite well.

//Ed

Received on Tuesday, 30 June 2009 14:02:18 UTC