Archweb DB Schema
In case you thought a distro website doesn’t have much complexity behind it, I present you with the database diagram for the archweb Django application that is the face of the Arch Linux website.
I created this as a way of wasting time putting off other things (hey, at least I can admit it!) as well as trying to figure out a way to better show a sticky situation dealing with joins and relations in the data model. I don’t think there is much Django can do for me in this situation, but if you look for the common pkgbase column on both the packages and packages_packagerelation tables, you’ll see that there is an unstated relationship there. Given that the Django ORM has no way of easily doing these types of joins, people have taken very interesting approaches to solving the problem, and I wanted to do the same thing.
It is a shame because with something like Hibernate for Java or even SQLAlchemy for Python, this can be done out of the box. It comes down to the inability to collapse outer joins when querying across one-to-many relationships. This means for a 50 page package search on the website, we have to do 50 more SQL queries just to find the package maintainer list.
On a very much related note, this diagram and the ones I created a while back detailing the iPhone SMS DB layout were created in MySQL Workbench, which is a pretty handy piece of software, even if your preferred database is not MySQL.
See Also
- Recent Archweb Improvements - August 27, 2010
- More Archweb Work - March 10, 2010
- Archweb gets a refresher - January 31, 2010
- Arch Package Visualization - June 23, 2011
- python-pgpdump, a PGP packet parser library - March 8, 2012