Help with database planning
ben+python at benfinney.id.au
Sat Nov 14 14:23:48 CET 2009
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
> 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 |
More information about the Python-list