Order of Precedence Schema

[jducoeur]

Some time ago, msmemory and I sat down and hashed out a tentative schema for an Order of Precedence database, designed to reflect all of the information she is currently tracking by hand. I've been busy enough on CommYou and other projects that I haven't managed to code the thing up yet (the difficulty of having a thousand interesting projects on tap at any given time), but I don't want to lose track of it. And since the cats keep knocking over the whiteboard, I'd better transcribe it before they manage to erase it completely.

So the following are our notes on the OP schema. It's a bit informal, but should be good enough to get the point across. It's described in terms of data type; these might or might not all actually wind up DB tables, but they're the conceptual objects and relationships we need to track. (In particular, when I say "Enumeration" below, it means a relatively finite list, but they might be represented as either real enumerations or small tables.) Note that most pointers may be null -- the sad reality is that information is often incomplete. It's by no means final, but it's mostly right.

This is potentially interesting to programming or heraldry/OP geeks, and probably not to anyone else.

In no particular order (well, in the order they wound up in the white board sketch):

Crowned Head
Type: Crown Type
Name: SCA Name
Group: Group

Crown Type
Enumeration -- King, Baron, Prince, etc.

Group
Name: String
Type: Group Type
Contained By: Group

Group Type
Enumeration -- Kingdom, Barony, Shire, etc.

SCA Name
Name: String
Person: Person
IsRegistered: Boolean
Gender/Type: Gender/Type

Gender/Type
Enumeration: Unknown, Male, Female, Collective

Person
Primary Name: SCA Name
Resides In: Group
Current Mundane Name: Mundane Name
Registered Arms: String
Is Collective: Boolean
Is Living: Boolean

Mundane Name
Name: String
Person: Person
Gender/Type: Gender/Type

Award Level
Enumeration: Peerage, Upper Kingdom, Lower Kingdom, etc.

Award
Name: String ("Laurel", "Crescent", "AoA", etc)
Group: Group
Level: Award Level

Court
Crown 1, Crown 2: Crowned Head
Date: Date
Name of Court: String (optional, eg, "First Court of TRM")
Event: Event
Sequence: Integer (eg, first, second, third court of the event)

Event
Name: String
Hosting Group: Group

Bestowal
Court: Court
Recipient: SCA Name
Award: Award
Sequence: Integer
Data Source: Data Source (where we got the info from)
As Part Of: SCA Name (collective)

Data Source
Enumeration -- Court Report, Heard from Recipient, Other Kingdom OP, etc

Comments

[rufinia.livejournal.com]

Out of curiousity, why do you think the "mundane name" is necessary?

[tpau]

i would think if nothing else it ties together two personae of the same person

[msmemory.livejournal.com]

Right.
Freydis Ragnarsdottir and Luke Knowlton, both personae of L. Pearson.

It's also useful for disambiguating two people with similar/identical SCA names. ("Elizabeth of Ostgardr, mka Liz Doe; Elisabeth of Ostgardr, mka Beth Smith.")

[rufinia.livejournal.com]

I don't know if that's neccesary. I don't know the structure, but the Midrealm OP combines them- in my entry, my AoA came under "Typheinne de la Croix" but the Court barony is under Aurelia Rufina, and either name takes you to the combined entry under Aurelia Rufina with a note "WAS Typheinne de la Croix." If people would rather not have thier mundane names involved, I don't think it's needed.

[msmemory.livejournal.com]

You still need some name under which to centralize each person's stuff. I could see an argument for using the SCA name, but I think it makes more work. Some people change their names several times, and I often see lots of spellings even of one correct name. I could imagine over the course of several years seeing Typheinne de la Croix, Typhenne de la Croix, Typheinne dela Cross, Typheinne de Northshield, etc.

[eclecticmagpie.livejournal.com]

The mundane name is a useful way to do this -- though not completely watertight (SSN would be better for most people), but it doesn't need to be _visible,_ which seems to be Aurelia's complaint.

[msmemory.livejournal.com]

Mundane name is not going to be visible to the average user. It's part of the under-the-hood records only (as is gender).

[tpau]

can you at least make persona gender availible? us scribes needs it...

[jducoeur]

Hmm. That's up to msmemory, but keep in mind that it's one of the fields that probably isn't going to get populated very often, because we usually don't have anything more than the name to go on. And remember that this won't happen until *after* the award, so at best it'll only be available for higher-level secondary awards, not for AoAs.

So even if she decides to make it public, it's not going to be useful to you very often...

[dagonell.livejournal.com]

How do you handle the case of the mundane name changing? Ghod knows, I've seen enough cases where Miss Jane Doe who is Lady Jane of Herplacename marries Mr John Smith who is Lord John of Hisplacename and not becomes Mrs Jane Smith, but also Lady Jane of Hisplacename.
-- Dagonell

[jducoeur]

Note that Mundane Name is a separate table that points to Person, and "Current Mundane Name" is a pointer from Person to Mundane Name. That reflects exactly this point -- there may be multiple Mundane Names, of which one is thought of as "currently best".

Not that we usually have that much data (as previously mentioned, we typically *don't* have the mundane name, much less two of them), but the structure can cope with it if we do...

[jducoeur]

I think there are a couple of fundamental confusions here, the biggest of which is that, as it says at the top, all fields may be null. That's technical speak for "optional". Essentially *all* of this data is optional -- indeed, mundane name is often not known. But it *is* often very helpful, which is why it's recorded when we have it.

Also, as others have pointed out, this is *not* describing what this looks like to the public, it's describing the internal database schema. Many of these fields are for internal use only, not for public consumption. Mundane name is one of them.

So (a) if people don't want their mundane names involved, they can say so and that can simply be left out (if it was known in the first place, which it often isn't), and (b) even when they are known, they're not visible.

In general, you seem to be interpreting this as what the OP *looks* like, which it isn't -- the look isn't intended to change. It's just an under-the-hood change to the way the data is managed...

[rufinia.livejournal.com]

well, yes and no.

If it's not visible, it's still there, which could make people uncomfortable (And I'll say it makes me uncomfortable. I'm not sure why, but it does). And if it's not neccesary, then why have it at all? True, mundane names are less changable than SCA names, but not by a lot, and you are more likely to have repeats in mundane names, so having one as a unique identifier doesn't seem like the most elegant of solutions.

[jducoeur]

True, mundane names are less changable than SCA names, but not by a lot, and you are more likely to have repeats in mundane names, so having one as a unique identifier doesn't seem like the most elegant of solutions.

This is just plain not true. SCA name stock is quite small compared to mundane, and much more prone to ambiguity. You of all people should know this -- your mundane first name is completely unique in my experience, but your SCA name is barely even unique on my friends list.

Moreover, there's a multiplicative effect: having two pieces of data reduces the ambiguity by orders of magnitude over having only one. If we were *just* using mundane name, it might be only so-so -- but having both reduces the chance of confusion to *very* nearly zero.

And seriously: I think you're making a mountain out of a molehill. You don't want it tracked, say you want it out of the data. It isn't necessary, but it *is* often useful. ("Necessary" is a pointless term, and a distraction. The OP is not "necessary" at all. It *is* helpful. So is having more data behind the scenes so that questions can be researched when they come up.) It is absolutely, explicitly optional in the schema, and non-public.

I'm sorry if that's not good enough for you, but given those facts, I think your concern is going well beyond the rational. This isn't some government agency we're talking about here: this is just a private project to correlate the existing notes and files into more manageable form. Heck, in the new system it'll be much *easier* to take your mundane name out of the data, because it'll only be in one place, rather than potentially scattered across many...

[jducoeur]

It's also worth noting: you seem to be fixated on the idea that "mundane name is the unique identifier", which has nothing to do with reality. The truth is that the OP is always, by the nature of the system, a gigantic muddle -- we don't *have* unique identifiers, nor are we ever likely to get any. There is a variety of highly-scattered, highly-ambiguous information.

So when problems do arise -- and they *do* arise moderately often, and are usually quietly addressed by the OP-keepers -- they need all the information they can get their hands on to resolve them. Mundane name is *one* element of that, and it can prove handy when it is available.

This isn't about some massive government database tracking people by mundane names. This is about trying to do a fairly hard job, using whatever information can be scrounged up. The point of the DB is to make that scrounging process a little less arduous...

[fosveny.livejournal.com]

The mundane name can't be the unique identifier. I'm sure we have more than one "John Smith" in the SCA.

The SCA name, if registered, is supposed to be unique. It says so right here on the label. Of course, 'supposed to be' is wishful thinking - there are a handful of cases where we have duplicates, and even one duplicate is enough to screw up a database that wants a unique field.

It's not just highly scattered and highly ambiguous. It's also highly incomplete.

[jducoeur]

The SCA name, if registered, is supposed to be unique. It says so right here on the label. Of course, 'supposed to be' is wishful thinking - there are a handful of cases where we have duplicates, and even one duplicate is enough to screw up a database that wants a unique field.

More importantly, you can't count on a registered name, and unregistered names certainly are *not* unique. Having two "Joe of Carolingia"s historically is downright easy. (Indeed, imputed SCA names are especially hard to distinguish.)

So yes -- incomplete, ambiguous, the whole nine yards. Hence, the DB needs to be extremely flexible and tolerant of crappy input data, because that's what we have to work with...

[tpau]

when you start working on this, can i help?

[jducoeur]

Probably, but at this point I haven't even decided for sure what to implement it in. (Rails and Hibernate being the leading contenders, for very different reasons...)

[baavgai.livejournal.com]

I'm unclear of the meaning of "Crown 1, Crown 2" in Court. I would think that would warrant two separate records?

For residence, I'd recommend making a separated entity for (Date, Person, Residence) so that you can be historical about it. e.g. "is that the same Erik that used to be in..."

Event probably also wants a date, if not more.

Also, a quirk of the SCA. You'll probably want gender on both the Person and the "SCA Name" entity. You may also want a time stamp on SCA Name, because people change...

[dsrtao]

SCA Name should also include:
Alias: string

which denotes how a person is commonly called when it's not a straight substring of some part of their Name. Good for searches.

[eclecticmagpie.livejournal.com]

Good point. Also, more than one such alias -- nicknames change as well, and some people have more than one anyway, as, for example, Fryderych (many spellings) known as Fritz, aka "that guy in the plate armor".

[jducoeur]

Hmm. Maybe, although I think of that as an alternate "SCA name" entry, which is more flexible. (People often have several common nicknames, so a single string isn't necessarily sufficient.) It's true that that loses a little data (the fact that a particular nickname typically adheres to the persona rather than the person), but I doubt we care enough to distinguish that.

It's possible that we might want to add an "Is Nickname" flag to SCA name, though, to reflect the subtle status difference -- that's worth chewing on...

[jducoeur]

I'm unclear of the meaning of "Crown 1, Crown 2" in Court. I would think that would warrant two separate records?

No -- think about it. This is reflecting the fact that a Court has either one or two Crowned Heads presiding over it. It might be the King and Queen, or just one of them; it might be a Baron who rules by himself with no Baroness; etc.

That being the case, it's not worth putting in a join table -- it's simply up to two references in the main Court entry. (Possibly both of which are null, in cases where we aren't sure *who* presided over that Court, or weird edge cases where there isn't really a Crowned Head involved.)

For residence, I'd recommend making a separated entity for (Date, Person, Residence) so that you can be historical about it. e.g. "is that the same Erik that used to be in..."

Possible, but this isn't information that we've traditionally tried to track -- Caitlin makes some effort to pay attention to where someone is currently living, but makes no pretense beyond that. So it isn't clear that it's worth putting in the additional history.

Event probably also wants a date, if not more.

Possible, but by no means certain. Event dates are a pretty complicated question when you consider all the variability that comes in there. And really, it isn't what *matters*. Remember, this isn't a be-all and end-all database -- it's just what matters to the OP. So the date of the Court itself (which is much simpler) is what matters: recording the other complexities of the Event is probably out of scope. And this thing is complicated enough -- we're trying to avoid scope creep.

You'll probably want gender on both the Person and the "SCA Name" entity.

No, that's accounted for, but it's worse than that. Note that there are separate gender fields for both SCA Name and Mundane Name -- which reflects the fact that *both* the SCA and real-life gender are mutable. Gender isn't on Person because that's insufficient: if we're going to track mundane gender at all, it has to be related to their full mundane identity at the time. (You think of these things when you live in New England.)

You may also want a time stamp on SCA Name, because people change...

Yeah, but we don't track the timing of that. We rarely know the timing, and it's typically not a very clean break anyway -- name changes often happen gradually over the course of years. Note the structure of the DB: a Person can explicitly have many SCA Name and Mundane Name records pointing to it, deliberately, with one of each designated as "current primary".

So we are keeping all of that changed information, we're just not worrying about the timing, because we rarely know it with enough precision to be worthwhile...

Dates, Bestowal to Branches, and Groups or Branches

[peregrinning.livejournal.com]

I'm having a hard time understanding the relationship of this database to the calendar. I don't see date fields where I expect them. I think you're focusing all date information at the "court" data type, which makes some sense. "Event" feels slim without dates or recurrence number (i.e. GNEW LXVII)... I think name of court should at least be created from the data in the court data type (i.e. "#th Court of Crown1 and Crown2 at Event on Date") so that you have something to select from for the bestowal data type, and something to list in the output.

I would also want dates for the founding/creation/elevation/retirement of SCA branches as well, though you may see that as part of the bestowal concept. Since this is focused on court reports, a way to connect that sort of court business is probably in your mind already. I think it would mean that "recipient" might be the name of an SCA branch.

I use "SCA branch," probably because my work as a cartographer forced me into a too-close reading of the Midrealm Seneschal's handbook. :-) "SCA Branch" helps to separate the standard branch types (Barony, Shire, Canton, etc.) from the many other groups in the SCA (Guilds, Households, etc.). I have no idea if that usage is widespread.

Re: Dates, Bestowal to Branches, and Groups or Branches

[jducoeur]

"Event" feels slim without dates or recurrence number (i.e. GNEW LXVII)

Yeah, but that's intentional. Remember, this is very OP-focused, with just the data we typically care about. Recurrence is a messy and weird topic, so we'd prefer not to get into it. (Eg, is Falling Leaves the same event that used to be Ladies' Champion Tourney? And what occurrence *is* that up to anyway? Most recurring events don't actually keep track.) So simply putting that into the Name field is generally sufficient for OP purposes, without wrestling with hard and out-of-scope problems.

Also, note that Date is on Court because it's relatively clear -- Courts are never (?) held over multiple days. (And we would probably count them separately if they were.) Dates of Events, by contrast, are fairly messy. And again, it's out of scope: what we *care* about is the Date of the Court, with the Event mostly as a linking placeholder and a name.

I would also want dates for the founding/creation/elevation/retirement of SCA branches as well

Again, interesting but probably out of scope, at least for the project as currently conceived. It's not part of the primary OP, so we're not worrying about it here. (And it's fairly easy to add later, if we decide we really give a damn.)

"SCA Branch" helps to separate the standard branch types (Barony, Shire, Canton, etc.) from the many other groups in the SCA (Guilds, Households, etc.). I have no idea if that usage is widespread.

I confess, I don't think it is. To me, a "branch" is a formal geographic arm of the Society -- I've rarely seen it used otherwise, and I'm pretty sure *I* never use it otherwise. I do refer to other kinds of *groups*, but the difference in terminology is a significant one to me...

[goldsquare.livejournal.com]

I can see a lot of other fields to play with. For example:

Joe gets a Laurel in the East, moves to the Midrealm... how is that recorded? Comes back to the East, resigns. Or is banished. Do you want to record each of those, somehow?

I haven't gone through the entire data structure like that. Would it help?

[jducoeur]

Joe gets a Laurel in the East, moves to the Midrealm... how is that recorded?

It's not -- but it's not data that we've ever *tried* to track, so it's not clear that we care. msmemory puts some effort into trying to keep track of where someone currently lives, but beyond that it starts getting into "more trouble than it's worth". We talked about it, but decided it was out of scope. (And this is big enough that we are deliberately trying to keep the scope limited to data that she's found that she cares about.)

Comes back to the East, resigns. Or is banished. Do you want to record each of those, somehow?

Hmm. Okay, *that* is a relevant point. At the least, we probably need a "resigned" flag, because that it certainly OP-relevant; being stripped of titles may be a separate flag, or may get lumped into a common "no longer has this" concept. Not clear that banishment per se matters from an OP POV, but it's at least worth talking about.

I haven't gone through the entire data structure like that. Would it help?

Don't lose sleep over it, but if you happen to spot other edge cases, point them out and we can talk about which one she cares about. Thanks!

[goldsquare.livejournal.com]

I'll try to get back to it.

As for the dismissed point, there have been times when people have tried to use the OP not just for "who has what" but "How many X did Crowns Y and Z give out".

[jducoeur]

Oh, we're definitely tracking who gave what award to whom -- note that Bestowal points to Court, which points to Crowned Head. So that's a complex join (Crowned Head, Court, Bestowal and Award), but still pretty easy. (And with a total number of records only in the tens of thousands, complex joins don't scare me much.)

We're just not explicitly tracking that Joe spent time in the Midrealm in between those events. It might be sort of implicit in his having received an award from the Mid, but awards received there is the only respect in which we're really paying attention to out-Kingdom time...

[siderea]

Radical thought: why have separate SCA and Mundane name tables? Why not have a name table, with a bit for SCA vs Mundane? That way, when you're looking for someone, you can search one table w/o wondering if, e.g. "Jack Greene" is an SCA or a Mundane name? Also, it makes it trivial to return all of a person's identities.

[jducoeur]

Hmm. Intriguing suggestion -- I'll think about that. I tend to think of them as qualitatively different, but the similarity of the data fields suggests that that may not be as true as my gut says.

Playing Devil's Advocate: the difference in privacy levels, obvious in the discussion above, may be one good reason to keep the tables separate -- it makes it less likely that they will accidentally get wires crossed and expose non-public information. Indeed, the difference in terms of public/private may be the *most* important difference between the two...

[siderea]

An interesting point. (Normalization is great stuff, right up to the point it isn't.)

BTW, this brings up the point: how is this going to be used? Is there going to be a public interface on the internet? You're aware of the SCA, Inc's policy on systems that connect SCA name to legal name?

[jducoeur]

The intent is mostly to generate the static OP pages, which are currently maintained by hand, and to be a tool when msmemory needs to look things up. Trying to set things up live -- well, that might be possible at some point down the road, but it's definitely getting ahead of ourselves.

You're aware of the SCA, Inc's policy on systems that connect SCA name to legal name?

The only rule on the subject that I know is from the Chronicler's Policies, and states that you aren't allowed to *publish* the correlation. As emphasized in the discussion above, that is absolutely not intended here. Indeed, that's why I bring the issue up in the context of the tables -- I want to make sure it doesn't happen accidentally. The point of keeping track of what information we have is solely for the problem-solving part of the job, not the public OP. (Most people don't have the slightest notion how much of msmemory's job is dealing with problems over and above what shows up in the OP.)

I don't know of any other restrictions. Indeed, I'd be surprised to find any, given how many SCA regulations *require* you to track both SCA and mundane names for various purposes...

[dagonell.livejournal.com]

"Indeed, I'd be surprised to find any, given how many SCA regulations *require* you to track both SCA and mundane names for various purposes..."

*Snort!* Yeah, like shire voting procedures! This was recently a PITA in our shire during elections. Only 'eligible' members can vote. Who's an 'eligible' member? A paid member within the zip codes. Which means you need to see both SCA proof of membership like a TI subscription AND mundane proof of residence like a gas bill, but you're not allowed to correlate SCA name with mundane name, oh no...
-- Dagonell

[jducoeur]

As far as I can tell, you *are* allowed to make the correlation -- you just aren't allowed to publish it publically. Did you come across any rules that say otherwise? (That would, obviously, be relevant here...)

[cellio]

Most of the time we won't know a mundane name but we will know that A and B are the same person (different personas, either sequential or concurrent). It would be good to have some way for that to all sort together -- if I'm considering writing someone in for an award, I want to know if he already has it and I don't want to play guess-the-alternate-persona. In the cases where you know a mundane name (e.g. if he's registered his name then the heralds have that in their DB), fine and dandy. If not, I would think you would need a way to cons up a unique ID.

Is it useful to have a place to hang (where known) the members of a collective?

[jducoeur]

Well, I suspect that mundane name simply isn't going to be available publically, for the reason that's obvious from the above: there are too many people who would bring out pitchforks. Besides, as siderea reminded me, it's against SCA policy to publish the correlation publically. So that's a non-starter. (And wasn't the intent in the first place.)

That said, the system is explicitly designed for cross-references. So if you know one of a person's SCA names, and that name is known to the OP, it should have all of their awards collated together and links from all of their names. That makes most cases manageable. If all you know is the mundane name, you're out of luck, but that's an unusual case.

Is it useful to have a place to hang (where known) the members of a collective?

Well, it's currently tracked where feasible, sometimes quite laboriously. Since the DB is mainly intended to cope with all current practice, we definitely want to incorporate that. (Note, however, that it is typically done on an award-by-award basis -- this is a hassle, but necessary given the changing composition of collectives. That's why the "As Part Of" field is needed on Bestowal.)