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

[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...