last year, at the end of February
Using schemaspy to generate a ER diagram of OpenX
Posted by pbirnie under technology
I maintain that the fastest way to understand a new system - is look at the ER diagram - this gives you the names of the entities (and hence classes) and the relationships and cardinality between these entities. I am now working on openx and the schema has lots of tables and no referential integrity. Also it seems there was no database coding standard 
- as table names have mixed singular(eg. ox_channel) and plural names(ox_banners), primary keys and foreign keys have no standard such as _id (zoneid is the same thing as zone_id). The name of the key also changes (ad_id is the same thing as banner_id).
java -jar schemaSpy_4.1.1.jar -t mysql -u root -p omitted -db openx -dp ./mysql-connector-java-5.0.8/mysql-connector-java-5.0.8-bin.jar -host 192.168.2.2:8889 -hq -o openxshemadump -meta openx_schema_meta.xml
generates this ER diagram
March 1st, 2009 at 9:37 am
You can’t assume that an Open Source database was ever “designed”. This one evolved over many years (8. 9?) helped by many coders.
During it’s evolution, the world in which it lived changed. Someone decided that Banners should be Ads and with the best intention they went off to make this change globally but got diverted by more pressing matters (as you do when working in an “Agile” fashion
).
Take a group of coders and watch them debate standards for years without ever settling for any. If they do, it takes time for the new standards to completely replace old ones and any interim snapshot reveals a mottled pattern.
Changing standards is easy on paper, just take that table or column name and change the spelling, voila the schema looks so much better! Now create your upgrade script and watch the users shake their fists at you because your small change has resulted in 40 minutes of copying records and re-indexing or the white-screen of death when their webserver times out.
The real Open Source world isn’t as simple as a relationship diagram, its full of real people with lots (and lots) of real data.
To see if the evolution has improved the OpenX schema (or not), go back and compare it (and the code) to its previous incarnations: phpAdsNew, Max Media Manager v0.1, Max Media Manager v0.3 and OpenAds.
At least the preferences (plural) table is now a name/value pair object instead of the 52 column monstrosity that the old preference (singular) table was.