Relationship Schema? Or am I complecting?

General
1

I’ve got a bunch of facts about people and organizations, and I want to define relationships between those people and organizations (kinda classic Prolog stuff, really), and I’m trying to figure out if this is best done with its own entity(-ies) or if I’m missing something cleaner. I’ve ended up with a schema that’s something like:

[fact-id][Entity-A][Relationship][Entity-B][dependency-ids]

My facts are things like [Fred][Supervises][Bill], [Acme][Employs][Fred], [Acme][Employs][Bill], [Funco][Employs][Bill], etc.

The facts can be independent of each other but need to be able express when the facts are connected. Maybe Fred is Bill’s supervisor at Acme, but Bill is Fred’s supervisor at Funco, or maybe Bill supervises Fred at both places. Here’s a potential set of facts:

[1][Acme][Employs][Fred]
[2][Acme][Employs][Bill]
[3][Fred][Supervises][Bill][1 2]
[4][Funco][Employs][Fred]
[5][Funco][Employs][Bill]
[6][Fred][Supervises][Bill][4 5]
[7][Funco][Employs][Joe]
[8][Joe][Supervises][Fred][7 4 6]

If I want a list of Bill’s supervisors, I should see Fred. If Funco fires Fred, Fred still supervises Bill but only at Acme, and Joe no longer supervises Fred, because he’s fired. Also, if just Bill gets fired, Joe no longer supervises Fred (let’s say because Fred only supervises supervisors).

Have I over-complicated this? Originally, I was just thinking:

[Entity-A][Relationship][Entity-B]

But I feel like I have to have the dependency list because A and B can swap their relationship under different contexts.

2

Hi Blake,

The dependency lists, employs, and supervises relationships on companies and persons could be replaced by entities representing the relationship. e.g. (angle brackets represent entities) imagine employee entities like this:

{:employee/company <Acme>
 :employee/person <Fred>
 :employee/supervises #{<Bill>}}

Then you can simply retract employee entities when employment is terminated and all supervisory relationships in that employee role will be gone, too.

By no means is this the only way, but this is how I prefer modeling scenarios like this.

3

Yeah, I can see a lot of value in that approach. The situation I’m in, however, is that I don’t know what the relationships will be. I oversimplified my example too much, I think.

Probably the key thing is that we have customers in the DB, and there has to be a way to express a working relationship between customers and providers, but also an ownership relationship between customers and either providers or organizations. Like, in a financial business, the broker has his clients he works with, but the firm “owns” them. He’s not supposed to run off with them.

But he might have his own side business with customers, too.

That’s just one possible set of circumstances. There could be relationships between customers, between providers, and between organizations. Relationships like “employee” that you outline is one possibility, of course, so I could create a new entity like the one you describe for every relationship that might emerge. (Employee, subcontractor, team member, parent corporation.)

I don’t really know. I may have erred on the “too flexible” side. =)