Dave wrote:jwhouk wrote:AmriloJim wrote:And the addition of the Kitahara kozuko means the next iteration of JW's relationship chart will be a cork board covered with Pst-It notes.
Hey, I didn't say it was going to be EASY to keep that up.
Heck, just making it into a
database boggles the mind.
Well, that's a problem that the industry solved decades ago.
It's a relational database.
I know that's supposed to be a pun, but it actually is a pretty simple database design.
tPerson: PersonID autonumber primary key, PersonIdentity text
tPersonName: NameID autonumber primary key, PersonID foreign key, Name, StartDate optional date, EndDate optional date
(Yes, I threw in supporting one person having multiple names - aliases, marriages & divorces, adoptions, nicknames...)
tRelationshipType: TypeID autonumber primary key, TypeName text
tRole: RoleID autonumber primary key, TypeID foreign key, RoleName text
tRelationship: RelationshipID autonumber primary key, Description text, TypeID foreign key
tPersonRelationship: PRID autonumber primary key, PersonID foreign key, RelationshipID foreign key, RoleID foreign key, StartDate optional date, EndDate optional date
(I used to do this stuff for a living. And if anyone's aware of the debate over natural versus artificial primary keys, my position is that you try to avoid ever letting anyone even SEE the primary key, let alone enter it - because if they see it, it will acquire meaning; if it has meaning, it can be wrong or can change; and changing a primary key is a messy business.)