No, that data is *not* simple

[jducoeur]

msmemory and I just sat down and sketched out the schema for an East Kingdom Order of Precedence database. Done right, it looks to be 14 tables -- discounting simple enumerations and trivial joins, it's still 10 tables of significant data. Really drives home how complex a model we've evolved.

The real "only in the SCA" moment, though, was realizing that each Person record potentially relates to multiple SCA names *and* multiple mundane names, of arbitrarily different genders...

Comments

[rufinia.livejournal.com]

I know the answer to this is probably yes, but have you looked at the Middle Kingdom OP? I don't know if they worry about mundane names at all, but it's a good system (and will even calculate the Order of March: I am 868). They do have it set up so it will consolodate multiple names. For example, if you search "Aurelia Rufina" you get my entry with a note that says, "Was Typheinne de la Croix" and if you search for Typheinne, you get to the same entry.

[msmemory.livejournal.com]

We do have that in the schema, yes. Multiple names, and even variant spellings, a viewpoint tainted by my years as Historian.

[mikekn]

Fun fun!

I'd love to see the final schema at some point.

[asim.livejournal.com]

IF you want to take a peek at Atlantia's OP schema. let me know, and I'll ask for permissions. I'm working with it for a project, and had to get the nickle tour of how it's laid out. It's...not quite how I'd do it, but it might give some ideas.

[crschmidt.livejournal.com]

Huh. People still think of things as tables? I'll admit that Django has caused me to think entirely in 'objects' -- and the trivial joins just disappear in my mind when I do that. I can't imagine programming without an ORM anymore.

[jducoeur]

I confess, I traditionally think more in terms of objects -- I'm just getting back into the relational mindset after many years of mainly being focused on the object side. I've been mostly an in-memory-DB guy, so I prefer to think in terms of objects, which are nice and efficient in memory.

That said, the reality is that ORMs can get you into a lot of trouble if you're not careful with them. I mean, CommYou is built on top of an ORM (Hibernate), and it's quite possible that the OP system will be as well, but that's only driven home that you can't depend entirely on the ORM if you want to be efficient.

The thing is, OO programming and relational programming are fundamentally different at the optimization level. Designs that are absolutely right in terms of objects are just plain *dumb* in terms of the underlying relational math. The result is that, even though I'm writing in Hibernate, I'm doing more and more of it in HQL, its internal query language -- which, while it *talks* in terms of objects, *thinks* in terms of tables. Having started out doing stuff at the OO level, I've found that 75% of my use cases optimize poorly that way.

The real issue, in my mind, is whether you are thinking about serious joins, which are a very relational way of doing things. It doesn't affect the schema all that much, really -- a fully normalized relational database looks very much like a properly factored OO data model. But how you do fetches is wildly different. For object-by-object fetches, the ORM way is quite nice: clearer, easier, with no downsides. But when you're fetching complex bunches of data at once, and want to do it efficiently, there's nothing for it but to do it in relational style, because the ORM doesn't usually give you an OO way to describe complex fetches efficiently.

(Or to put it another way: yes, you're correct for single-level joins. But I find that most interesting problems are more complex than that when I need to return more than one object at a time, and the OO viewpoint leads to inefficiency in those cases.)

Just to present an example -- the most fundamental output of the OP system is the alphabetic list of people and their awards. Say I want to show all the people whose SCA name starts with "M". That hits the SCA Name, Person, Bestowal, Court and Award tables for several hundred SCA Names. To fetch that efficiently, you have to be able to express that whole query as a unified operation -- which SQL/HQL is designed to do, and OO languages generally aren't.

All that said: I haven't actually worked with Django, although I've looked at it a little. It's possible that it deals with this better, although I'd be a bit surprised -- the fundamental issue is a tension between OO and relational ways of looking at sets of data, and being able to express complex set math in a unified way. I'd welcome pointers of how to do this *well* in an OO-oriented model...

[jducoeur]

So you got me curious, and I did a bit more digging into Django, to understand it better. It looks like it *can* do efficient lookups (unlike some ORMs) -- but only if you kind of think in table terms. The select_related() mechanism, used judiciously, ought to permit efficient programming, but it does require that you be thinking about the set-math nature of your query when you do it, and that's more or less when I consider the difference between thinking in objects and thinking in tables. In particular, using select_related kind of requires a bit of abstraction-breaking that is unfortunate but very useful sometimes.

That said, it's rather interesting, and a more elegant framework than many. I'll have to ponder Django as a possible basis for the project...

Lock-in

[metageek.livejournal.com]

Personally, I prefer designing in terms of tables because they're more portable; you can write new apps that access the database without being locked into the ORM framework you started with.

Re: Lock-in

[jducoeur]

Yeah, that consideration has occurred to me. But to be fair, most ORM frameworks worth their salt nowadays include good tools for reverse-engineering from existing databases.

Indeed, one thing I love about Hibernate is that it lets you start from an existing DB, pure metadata files, or Java code, and will help generate the other two levels based on that. That's a fine attitude, and while they can't eliminate *all* the work of porting an old DB, they do make it as painless as possible...

I can haz input, please?

[fosveny.livejournal.com]

Would you be amenable to including either Robin Gallowglass or myself, or both, in your design team?

If done right to start, the design could be incorporated into a few other projects that he and I are currently the keepers and maintainers of, and some prospective projects.

I'm mostly talking about big-picture issues. Thinking of it as part of a larger whole, not as just an OP database.

Re: I can haz input, please?

[jducoeur]

Um -- in what respect?

I'll be frank: past attempts to do this have largely bogged down in becoming over-ambitious and bureaucratic, trying to do too much at once. We are *not* amenable to the project growing particularly, nor to scope creep. The only reason we see it as likely that it's going to happen without strife is that we're keeping this focused and (literally) in-house, where the two of us can work very closely on creating something that is actually going to be focused on msmemory's needs as the principal user of the thing.

So I need to know *exactly* what you're talking about here...

Re: I can haz input, please?

[fosveny.livejournal.com]

My intention is neither scope creep nor bureaucracy.

I'm talking about letting me look over the schema and possibly making a few change suggestions before implementation, when it's trivial to change the schema.

"If you add this field, it will be easy for other kingdoms to use" and "if you move this field over here, this can integrate with a later awards recommendation database easily" and "have you considered what you're going to do when award names change, can you track what they were called when given?" sort of suggestions.

I certainly don't want to try to insert myself wholesale into the project. I'm going to have enough to do with oscar (http://oscar.sca.org) and being Wreath Sovereign. I really, honestly do mean "a few suggestions".

Re: I can haz input, please?

[jducoeur]

Fair enough -- frankly, I'm planning on posting the schema before I start anyway...

Re: I can haz input, please?

[alphaggek.livejournal.com]

I'd like to second what Istvan has said. I can't say it any better.

Re: I can haz input, please?

[fosveny.livejournal.com]

While I'm at it, if you plan to use PHP, may I offer a module that converts between most of Da'ud notation ( http://www.scadian.net/heraldry/daud.html ), UTF-8, Western-1, HTML entities, or one way to straight ASCII with accents stripped?

Function calls, but easily converted to a class.