Help with database planning

[Ben Finney]

Juliano <jjunho at gmail.com> writes:

> We've been working with an ontology at my department […] I have been
> being pushed towards changing the basic plan and build a DB so that
> data access will be faster and easier for both the desktop GUI and the
> web app. Right now, I'm trying to work with sqlite, since it can be
> used as a separate file for the GUI and as a DB for Django (which may
> be the choice for the web interface).

You're also working with a relational database system, which will make
it relatively easy to migrate your database structure to a different
relational database system if that proves necessary.

> I have been redaing some books on DBs but I kind of get stuck when it
> comes to the normalization and the planning of the tables.

No shame in that; it's somewhat counter-intuitive, though it's an
essential topic when working with databases. Try the Wikipedia article
<URL:http://en.wikipedia.org/wiki/Database_normalization>.

> The problem is that we have basically four fields that can be arranged
> in a tree- like structure.

Tree-like structures don't fit into table-like relations very well; but
it's not hopeless.

> So, for ONE *concept*, we have, usually, MANY *slots*, each *slot* has
> ONE *facet*, and each *facet* can have MORE THAN ONE *filler*.
> Besides, some *slots* and *fillers* are themselves *concepts*,
> creating a sort of recursive reference.

Recursive references, on the other hand, are deadly to storing data
sanely in a relational database: while recursion is algorithmically
elegant, it makes for hideously slow operations querying, and especially
modifying, the database.

The focus, then, should be on teasing out a non-recursive, declarative
schema for the data. This is a well-known problem, with many possible
solutions. Several popular approaches (each with different trade-offs)
are “Adjacency list” , “Materialized path”, and “Nested sets”. Use those
terms in database-savvy circles and you'll get lots of explanations.

> What would be a good way to create tables for this situation?
> In fact, this is the first time I've ever tried to create a DB, so I'm
> completely lost.

This topic is rather orthogonal to Python. You would do well to seek
further guidance from a Usenet group such as <URL:news:comp.databases>.
Good fortune, and persist; you're going into databases off the deep end
with a problem domain like this :-)

-- 
 \       “I distrust those people who know so well what God wants them |
  `\    to do to their fellows, because it always coincides with their |
_o__)                      own desires.” —Susan Brownell Anthony, 1896 |
Ben Finney