Hi Erwin, Thanks for the offer of some help in looking at normalisation. I've now managed to read the hibernate docs, run some queries against my mysql ISAM database and make a few notes. It would be very useful at least if you wouln't mind checking, correcting and refuting some of the things I'm trying to document. My aim would be to have some more sound assertions about xwiki to build on? Here are some preliminary topics we might consider: 1. MySql | Hypersonic | HQL * remove all sql into stored procedures. I see mysql now supports them; don't know anything about hypersonic. * what language is this? It appears to be joining a property value classname to a StringProperty name property id.id? "select prop.value from BaseObject as obj, StringProperty as prop where obj.className='Blog.Categories' and prop.id.id = obj.id and prop.id.name='name'</sql>" A bit difficult to understand how this fits in with my mysql tables but I'll pursue this and a few other queries in the code. (PS. I've probably read it wrong anyway) * I think I understand that the back-end store is not very important for referential integrity, more a data dump which doesn't necessarily have to be very organised or have any semantics. I think the smart idea used to be to dump as much processing into the back-end, Oracle, Siebel, SAP and even DB2 etc. because only their prefered architecture would ever run reliably, never mind fast enough. I think still agree with that. Stored procedures are not part of the data model; operations are part of some business logic controller. I can choose to partition my MVC that way and (for performance,SOC and maintenance reasons probably), I have never come across a better way. So I think I'd like to ask how much processing should go into the back end? To keep lightweight, portable and upgradeable I see that minimum interfaces are required. I don't think I would ever use an RDBMS which didn't support stored procs. SQL syntax translation isn't too difficult is it? * I note that InnoDB (vs ISAM) is needed in mysql to support explicit foreign key constraints. a. Is this digging in to techniques which will end up as a lock-in? Or b. Foreign key constraints are one's best handled by the DB? Or c. Use surrogate keys wherever possible, only natural keys where it makes performance sense and avoid composite keys. However there *are* still FK constraints between table columns. IMHO they should be enforced ASAP in processing, ie. before stuff comes out of the database. * The XWiki documentation seems to refer to HSQL a lot. Are Xprtnet planning to stablise on that or HQL, both or other? 2. Hibernate * I see Ludovic has followed the joined-table syntax with slistclasses and dblistclasses. This seems the best option for inheritance. I haven't quite managed to get my head round when to use the union-table syntax? * The Hibernate documentation says one thing and does another when it comes to natural and composite keys. I suppose it's the same with me if you talk about address field sets: very inefficient to normalise so use a surrogate key. * I can only see one-table-per class as the least risk first choice model. At a quick scan, per hierarchy and concrete class options seem to have too many drawbacks. But when it comes down to it the Hibs use natural composites, then add an OID. I thinks OIDs are irrelevant to an RDBMS except when we have multi-valued fields stored as indexed lists or arrays. Then there is a number column eg. listitem number and this is what hibernate persists. * Anyway I assume there is a stable consensus for the time being on following some hibernate OR architecture? 2. Data Analysis * Anyway down to work. So we have something to work on, I've attached a dumped zip xls file of current xwiki PK data. It seems easier to read these things on a spreadsheet. Can you check that this is a fairly default set of data with a few additional pages? Because I have had to manually delete a few "question" and "answer" fields from both strings table and properties table when failing to build a FAQ, or othe reasons, it might be corrupt. * If we had a reference data set we could possibly infer a lot more relationships? * I have loads of questions, notes and assumptions but this is enough for one email. I'll send the next one ASAP. 3. Goals SPerhaps a specification of some AOP module for run-time data integrity checking? Might be useful and good AOP and annotation practice? Best Regards Jim -- Jim Stuttard