Hi bawolff, thanks for your work. I'm not very happy about the name "metadata" for the table. As far as I understand it, this is about file metadata. "metadata" suggests it contains information on pages (e.g. statistics). Please consider using a name that contains 'file', e.g. file_metadata. Thanks, Church of emacs

On 05/28/2010 08:03 AM, bawolff wrote: Excellent! We're glad to have you on board. (FWIW I'm working on Multimedia Usability, so I'll be watching what you come up with closely). Great, although perhaps we could consider other things too (see below). Okay, I just wrote a little novel here, but please take it as just opening a discussion. I think you should try for a simpler design, but I'm open to discussion. I'm familiar with how MySQL scales (particularly for large image collections). Commons has a respectable collection of > 6.6 million media files, but just to put that into perspective, Facebook gets that in just a few hours. If we're successful in improving Commons' usability we'll probably get some multiple of our current intake rate. So we have to plan for hundreds of millions of media files, at least. POTENTIAL ISSUES WITH TREE STRUCTURES Tree structures in MySQL can be deceptively fast especially on single-machine tests. But it tends to be a nightmare in production environments. Turning what could be one query into eight or nine isn't so bad on a single machine, but consider when the database and web server are relatively far apart or loaded and thus and have high latency. Also, due to its crappy locking, MySQL sucks at keeping tree structures consistent. If we were doing this on Oracle it would be a different story -- they have some fancy features that make trees easy -- but we're on MySQL. The most scalable architectures use MySQL's strengths. MySQL is weak at storing trees. It's good at querying really simple, flat, schema. BLOBS OF SERIALIZED PHP ARE GOOD You should not be afraid of storing (some) data as serialized PHP, *especially* if it's a complex data structure. If the database doesn't need to query or index on a particular field, then it's a huge win NOT to parse it out into columns and reassemble it into PHP data structures on every access. GO FOR MEANINGFUL DATA, NOT DATA PRESERVATION Okay onto the next topic -- how you want to parse XMP out into a flat structure, with links between them. I think you were clever in how you tried to make the cost of storing the tree relatively minimal, but I just question whether it's necessary to store it at all, and whether this meets our needs. It seems to me (correct me if I'm wrong) that your structure is two steps beyond id-key-val in abstractness: it's id-schema-key-type-val. So the meaning of keys depends on the schema? So we might have a set of rows like this, for two images, id 1234 and 5678: id: 1234 schema: AcmeMetadata key: cameraType type: string val: canon-digital-rebel-xt id: 1234 schema: AcmeMetadata key: resolution type: string val: 1600x1200 id: 5678 schema: SomeOtherSchema key: recordingDevice type: string val: Canon Digital Rebel XT id: 5678 schema: SomeOtherSchema key: width type: int val: 1600 id:5678 schema: SomeOtherSchema key: height type: int val: 1200 The point is that between schemas, we'd use different keys and values to represent the same thing. While you've done a good job of preserving the exact data we received, this makes for an impossibly complicated query if we want to learn anything. When you find yourself defining a 'type' column you should be wary, because you're engaging in Inner-Platform Effect. MySQL has types already. It seems to me that we have no requirement to preserve exact key and value names in our database. What we need is *meaning* not data. So we shouldn't attempt to make a meta-metadata-format that has all the features of all possible metadata formats. Instead we should just standardize on one, hardcoded, metadata format that's useful for our purposes, and then translate other formats to that format. The simplest thing is just a flat series of columns. In other words, something like this: id: 1234 cameraType: canon-digital-rebel-xt width: 1600 height: 1200 id: 5678 cameraType: canon-digital-rebel-xt width: 1600 height: 1200 WHY EVEN HAVE A SEPARATE TABLE? If we're doing one row per media file (the ideal!) then there is no reason why you can't simply append these new metadata columns onto the existing image table. This would make querying REALLY easy, and it would simplify database management. And of course metadata formats differ, and not all metadata fields need to be queryable or indexable. It would be perfectly acceptable to parse out some common interesting metadata into columns, and leave all the other random stuff in a serialized PHP blob, much as we have today. That structure could be recursive or whatever floats your boat. I would even argue that you should combine multilingual descriptions into the same column, possibly as serialized PHP or maybe just a simple string format. At Wikimedia, we do not do MySQL-based text search any more -- and for other MediaWiki installations that do, combining all description strings into one field is arguably a benefit. For Wikimedia projects, we can deserialize that field and break it out into languages when we feed it to Lucene. There could be good reasons to have separate tables for metadata, but I think we should carefully consider the costs. Sounds like a lot of work to make it efficient. Probably shouldn't be a high priority. Thanks a lot for presenting your design here in detail. If you want to take it to a wiki I can reiterate some of this debate on your design's talk page. -- Neil Kandalgaonkar ( ) <neilk(a)wikimedia.org>