The Rating Stone Reputation System Database

$Id: Database\040Design.md,v 1.56 2021/01/16 17:25:05 agmsmith Exp $

  1. The Rating Stone Reputation System Database
    1. Basic Ideas
      1. Changing Things by Making New Objects
      2. Single Table Inheritance
        1. Reusing Generic Fields to Avoid Space Explosion
      3. Direct References, Link Records, or Full Blown Lists
        1. Design Choice - Faster
      4. Retrospect
    2. About Posts and Groups
    3. Relationships
      1. Symmetry Dimension
      2. Approval Dimension
    4. Permissions Role Model
      1. Wildcard Language
    5. LedgerBase Classes
      1. LedgerDelete < LedgerBase
        1. LedgerUndelete < LedgerDelete
      2. LedgerApprove < LedgerBase
        1. LedgerUnapprove < LedgerApprove
      3. LedgerUser < LedgerBase
        1. LedgerCompany < LedgerUser
      4. LedgerThing < LedgerBase
      5. LedgerContent < LedgerBase
        1. LedgerPost < LedgerContent
        2. LedgerMedia < LedgerContent
      6. LedgerSubgroup < LedgerBase
        1. LedgerFullGroup < LedgerSubgroup
      7. LedgerAwardCeremony < LedgerBase
        1. Calculating Awards
      8. LedgerBirthday < LedgerBase
      9. LedgerDeathday < LedgerBase
    6. LinkBase Classes
      1. LinkOwner < LinkBase
      2. LinkReply < LinkBase
      3. LinkMedia < LinkBase
      4. LinkHomeContent < LinkBase
      5. LinkHomeGroup < LinkBase
      6. LinkRole < LinkBase
      7. LinkGroupRoleDelegation < LinkBase
      8. LinkGroupContent < LinkBase
      9. LinkSubgroup < LinkBase
      10. LinkRelationship < LinkBase
        1. LinkSymmetricRelationship < LinkRelationship
        2. LinkAsymmetricRelationship < LinkRelationship
        3. LinkOneWayRelationship < LinkAsymmetricRelationship
          1. LinkFriend < LinkSymmetricRelationship
          2. LinkEnemy < LinkSymmetricRelationship
          3. LinkSpouse < LinkSymmetricRelationship
          4. LinkParentChild < LinkAsymmetricRelationship
          5. LinkEmployerEmployee < LinkAsymmetricRelationship
          6. LinkFollower < LinkOneWayRelationship
      11. LinkOpinion < LinkBase
        1. LinkMetaMod < LinkOpinion
    7. Auxiliary Classes
      1. AuxLink
      2. AuxLedger
    8. Generating the Database
    9. Test Plan - A few Use Cases
    10. Change Log

Basic Ideas

Because I’m planning on implementing the reputation system as a BlockChain ledger application, or as a federated collection of systems exchanging records, the database is set up as a write once collection of object records and link records. Objects are the things the user can view and which have ratings (posted messages, lists of things, other users). Links implement lists of things, connecting a pair of objects together, one being a parent or container object (such as a list of book reviews) and the other being a child object in that container (such as a book review object). Usually rating points are also transfered when you create a link (perhaps describing how much you like that book review). I’m using separate link records rather than parent references in the objects so that an object can have multiple parents (useful for filing something under multiple categories).

Changing Things by Making New Objects

For example, if you want to edit some posted text, the system will create a newer object (they all have datestamps) with the new text and a reference back to the original text object record. Then when displaying the original text, it will search for newer versions and display that text instead. But new references (links) to the text (such as replies) will still reference the original text record, since that has a more constant identity then possibly yet more new text records. Advanced users can view a list of all those text records and see the change history.

Similarly when someone adds an item to a list, a link record will be added to connect the list and the item. To remove an item, the link record isn’t modified, instead a new deletion record is created, referencing the original link record.

Theoretically, when displaying the current state of things, you run through all the records in chronological order, adding and removing objects and links from the current state. In practice, we cache things. So an object record would have a flag that marks it as deleted or amended, and a link record would similarly be marked, and there can be cached forward references to more quickly point out the record with the latest information.

Single Table Inheritance

Because I don’t want to have zillions of tables in the database, and want to inherit functionality for various related record types (generic object > text object > book review object), we’re using the Ruby on Rails ActiveRecord “single table inheritance” setup. There will be one actual database table for objects and another one for links between objects, both have the Rails magic “type” field in each row to specify the particular subclass used for that row.

Reusing Generic Fields to Avoid Space Explosion

To avoid the common complaint about single table inheritance adding too many fields for subclasses, we put some generic number and string fields into the base class and reuse them differently for each subclass. Subclasses give those generic fields subclass-specific names by using the magic “alias_attribute” ActiveRecord function. So where the base may have a generic string1 and string2 field, a User subclass could rename those as FullName and EMailAddress, with “alias_attribute :full_name, :string1” and “alias_attribute :email_address, :string2”. ActiveRecord will then automatically build queries using the actual database field (string1, string2) names whenever we use the subclass object.

There’s a design choice needed for making lists of things. Should you make link records between a main object and sub-objects that apply to it, or should you have a second object as kind of a list header, and link the sub-objects to it? For example, a Friends list. You could link friends (LedgerUser records) to the main user (another LedgerUser record) with specialised LinkFriend records. Or you could make a LedgerListFriends object with a link to the main user, then attach the friends to that with generic link records.

Design Choice - Faster

Direct links are faster for the database, but need a lot of custom link record subclasses, but those are fairly cheap. The list header method just needs to give the list header a name and uses generic records, but requires more database operations. There’s also a third option of putting the link reference in the record as an id field, like friend_id, but that only works if you have one friend (useful in other cases, like having only one creator). We’ll go with the faster but more work custom link subclasses method, except for user defined lists which will use the list header with a name technique, and plain references where that makes sense. If the number of subclasses gets too big, this decision will have to be reconsidered.

Retrospect

In retrospect it may be simpler to combine objects and links into a class structure based on a single multipurpose object. Currently we have records for linking things together (such as linking a user and a thing together with a rating to say that that user has an opinion about that thing) and object records representing things (posts, pictures, etc). The links transfer reputation points. The objects contain reputation points. A multipurpose object design would make it simpler to have an opinion about an opinion, which is needed for meta-moderation. And we need meta-moderation to keep the quality of opinions up.

About Posts and Groups

When someone writes a post, they create both a LedgerPost object with the text and a link object (LinkGroupContent) to connect it to the group it is in. If they want it to be in multiple groups, they can just add more links. If it’s a reply to some other post, they add a link (LinkReply) to that post too. It’s a bit awkward and expensive in ratings points (each link needs points) having both a link to the reply and a link to the group, but both are needed. We can’t default to the group based on the group the previous message is in, since later on someone could add that previous post to some other group and your post would suddenly be visible to people you hadn’t expected. Also this discourages the evil of unnecessary cross-posting.

The links also award points to both the post and the group (the user decides how much to spend and how to split it), so that posts using the group contribute a bit to its upkeep. The group owners can spend the group points on the group’s behalf, perhaps reimbursing moderators for the points they use while doing their work (creating approval and deletion objects takes points). Of course, if the group’s points fall to zero, it can be garbage collected. Same for posts that aren’t referenced much.

I’d like to have small points awarded for just reading a post, but that won’t be practical since it would blow up the database size tremendously (each read would create a LinkBase record). Or maybe we could have a daily “tip” or donation operation that awards points to the list of posts the user read that day, and a variation of LinkBase that uses AuxLedger records to specify a bunch of posts to get a reward. Though that would make the backtracking from the post to donors awkward (not just looking for a connected LinkBase object) when doing awards ceremonies. So for now, you can “like” or maybe “cast a stone” for individual posts and award points manually.

So, what happens if you make a post that isn’t in a group? People can still find it by looking at the creator’s profile for all the posts they have written. There are no groups to allow people to read it, so it has a default permission role of Banned for everyone except the creator or owners (if the user attaches extra owners to the post). An alternative for ungrouped posts is to make them readable by everybody, including anonymous Internet users so search engines will pick them up. Which to choose? A default of Banned would force users to spend extra points to make their post readable in some group. A default of overly public would encourage more public discussion. I’m going to make them public, since we still require spending typically 1.0 reputation points to create an ungrouped reply. It costs twice as much (or you could make do with a shorter message life) to make it a reply in a private discussion, since you have the group link(s) to add as well as the reply link. People will still use groups since it’s worthwhile to reach a more relevant audience.

Relationships

We want to identify people based on relationships. The obvious one used by many social media is Friend. Another is Follower. We can also do Parent, Child, Spouse and that should about cover it for family relationships (grandparents are Parent of Parent of person, siblings share a parent, etc). We might want to add variations of friendship like boyfriend/girlfriend, work related acquaintance, divorced and so on but preferably without the extra complication of gender. Possibly besides birthday records, we’ll need death date records so a spouse is a widower when the other spouse is dead, but they go back to being married when they are both dead (they’re always married, but we’re showing what is important to their audience).

After discarding some earlier ideas on relationship implementation, it seems there are a two orthogonal properties of relationships we have to worry about for searching and security purposes: Symmetry and approval. All relationships will be represented in our database by a LinkBase subclass record listing two people (parent and child fields, though that usually isn’t the actual meaning) and how they’re connected.

Symmetry Dimension

Bidirectional or equal or symmetric relationships are like Friend where both ends of the relationship are an indistinguishable “Friend”. Same for Spouse. But Parent/Child is a pair that has distinctly different ends. So is Follower/Leader, or Employer/Employee. Those we’ll call asymmetric.

The main complication is that we have to search differently depending on the symmetry. If we’re looking for friends of person X, we have to search for all Friend records that have X in the parent_id as well as all Friend records that have X as the child_id. If we’re looking for all parents of X, we have to search the Parent/Child records which have X as the child. If we’re looking for all children of X, we have to search the same kind of parent/child records, but look for ones where the parent field is X.

So, the search technique changes and we need to know that Parent/Child searches look for the same type of record but search different fields.

Approval Dimension

The other dimension is approval. Sometimes we need approval from both parties to create the link; each approves their end using a LedgerApprove record. Other times we have a one way relationship, such as a fan of a movie star, and only the fan end needs approval (and that’s usually automatic).

One side effect of approval is that the reputation points assigned to the link only get counted when the link is fully approved. That gives incentive to do approvals, and opens the door to third party suggested relationships (be a fan of this brand and you get some points). Of course, instead of approving, you can delete the link, and the points spent on making the link are wasted.

Partially approved relationships are still useful. If only one end is approved, then for example instead of Friends we have admirer (the approved end) and admiree (the unapproved one). Or if both ends haven’t been approved, we have a suggested friendship (maybe a third party created the relationship record). One-way relationships still need approval of the originating end, the fan implicitly approves the relationship record when they create it for themselves. If someone else created it, then it still needs the originating end approved by the fan. The movie star end is automatically approved. Otherwise fans and haters would have their one way relationship rebuffed; a particular problem for awarding down points. No, the movie star or villain can’t delete one way relationships; like fleas they stick around for a while and annoy.

Permissions Role Model

The main worry is about posts. The system needs to decide who can read a post, who is allowed to reply, who can add a post to a private discussion group, who can delete it and so on. We’ll worry about other permissions later. After considering access control lists and even UNIX style permissions I decided that every post should be in a group or several groups. The group decides many of those things mainly by having a membership list. Even a person’s blog would be a group. Perhaps private direct communictions could be a group too, with just the two people in the conversation being group members.

The next big insight was to use permission roles. They can be put in a rough order where one more permissive role includes the things allowed in the lower less permissive roles, to save on having to grant each permission individually. In practice, each role is granted by linking the user to a group with a record that specifies the highest role they have.

  1. Banned. Can’t access the group or posts.
  2. Reader. Can read posts and see the group description.
  3. Member. Can see more group information, like membership lists. Some groups also automatically approve posts by members. Others require approval by a moderator. By the way, there’s no permission needed to request that a post be added to a group, so even a non-member could do that.
  4. Message Moderator. Can approve, unapprove (makes it pending approval again) and disconnect posts from the group.
  5. Meta Moderator. Can make anonymous comments on opinions about messages in the group.
  6. Member Moderator. Approves and rejects membership requests.
  7. Owner. Owners can be added to a group (and other objects) by a LinkOwner record. They have all permissions (including approving or deleting owners and moderators), except they can’t change the creator.
  8. Creator. Has all permissions to modify the object, including changing the creator.

We’ll use ten times the list row numbers here as the role priority, in case we later want to add new roles in-between.

When finding out if a user has a permission for an action allowed by a certain role, check for a match with the creator first (everything is allowed). Then see if they are in the Banned role second: permission is denied (if you want to explicitly deny a user access to the group, give them the Banned role). Then start searching at the most permissive role after Creator and work down to the lowest level that matches the role query. If they were not found in the search then they don’t have permission for the role. In practice, we’ll first look for some LinkBase subclass record that specifies roles and sort by role priority. Then try the more computationally expensive wildcards for each role.

The group would have a few settings related to those roles (stored in a separate database table rather than directly in the LedgerFullGroup record):

Wildcard Language

To identify a group of people by wildcards, we can make up a little language. We want to have expressions that say:

Perhaps a functional language like:

So you could have “Friend(Members)+Members” for friends of all members and the members too. An empty string would mean nobody.

LedgerBase Classes

This is the base class for persistent objects that the user may interact with. There is one database table for all objects of this class and subclasses. The name is short for LedgerObjectBase class. I tried to find other words for a short form, but there’s already an “Object” class and “Thing” didn’t seem serious or distinct enough.

LedgerDelete < LedgerBase

Used for marking possibly multiple objects as deleted. AuxLedger and AuxLink records are used to associate this LedgerDelete with all the objects (both LedgerBase object records and LinkBase link records) being deleted. Undelete is implemented by creating a LedgerUndelete record for the same target objects (was too messy trying to delete a LedgerDelete; that is not supported). Can have several LedgerDelete and LedgerUndelete records affecting the same object; only the latest unexpired one decides the target object’s status. So yes, if enough time passes, the LedgerDelete will fade away and the targets will no longer be deleted. Though most often the targets will have faded away too by that time. Or we could have a policy of not garbage collecting LedgerDelete and LedgerUndelete records.

You need to be the creator or owner of the object being deleted.

LedgerUndelete < LedgerDelete

Undoes a LedgerDelete and is otherwise similar to it. Besides being less confusing than deleting a delete record, this lets us more easily find out who is deleting a target. Also, we can undelete a different set of things than were deleted.

LedgerApprove < LedgerBase

Gives permission for pending links to start working. To avoid costing a lot of rating points for approving things like replies to your post or bulk approving posts to a group, you can specify multiple links with many AuxLink records. Also used for things like approving a request to join a group (the request is a LinkBase subclass record joining the person to the group). Both child and parent approvals are given, determined by whether you are the child or parent. To more strongly reject permission, delete the unapproved link with LedgerDelete (if you need to be asked for approval, you can delete it too).

LedgerUnapprove < LedgerApprove

Undoes approval for the specified links. Not usually useful, since it puts the links back in pending state. If you want to reject approval, try deleting the links. Otherwise similar to LedgerApprove.

LedgerUser < LedgerBase

Idenfities a particular user. We don’t have placeholder users (for celebrities and other external people); a group should be created by fans instead.

LedgerCompany < LedgerUser

Are corporations people? Not really, but we have them as users too in case someone wants to run a company web page or otherwise have a presence on this reputation system. As well, employees can list the LedgerCompany as their employer. Alternatively, they are a regular User but linked to a category of Companies.

LedgerThing < LedgerBase

Represents a real world thing like a book. Users can comment about it and award it rating points, but it never regenerates points (only Users do that). Attach content to it (like pictures or a post) to describe it better, using LinkHomeContent. Links to a classification group can rate it. For example, a book reviews group would have a link to the book to award it points. Can be in multiple groups, such as “My Books” and “World’s Best Books”.

LedgerContent < LedgerBase

An abstract base class for things which can be displayed to the user. There can also be replies to this thing, it can be put in a discussion group, added to a category list to identify what the topic of the content is, have rating points awarded to it, and so on. It is also usually visible to users and even external web sites, unless permissions (determined by the groups it is in) make it private.

LedgerPost < LedgerContent

A post made by a user. Contains some formatted text. They can also attach pictures and other media files with LinkMedia records. They’ll be displayed in a grid after the text, in order by link creation date. This is the object the user interacts with the most.

LedgerMedia < LedgerContent

A file uploaded by the user. Usually some sort of media, like a picture or video clip. Can be attached to multiple posts to make them more interesting, and referenced by external web sites as well. Byte size only limited by the points the user wants to spend to upload it. All kinds can display a picture, which can be clicked on to get more detail or play it if it is a video or audio file or download it if binary.

LedgerSubgroup < LedgerBase

A way of collecting related things together. Useful as a subtopic of a discussion group or a category for photographs within a classification tree. All content that fit the subtopic (decided by users and moderators) are linked to this object by LinkGroupContent records (even replies to posts already in the subgroup).

LinkSubgroup records specify the hierarchy of subgroups (actually a possibly cyclic directed graph), by linking parent group to child group. Multiple parents are allowed, so a photo taken in Canada in July 2020 could be filed both in a subgroup for North America and a subgroup for Year 2020. You could even make subgroups for each family member and classify the photos by who is in them.

There’s an optional home post (see LinkHomeContent) that describes the group in more detail. The home post usually will have a list of recent additions in the group displayed after it.

A subgroup object doesn’t have members attached or do permission role check operations; that’s delegated to the LedgerFullGroup(s) which control this subgroup by LinkGroupRoleDelegation records. If there are none then only creators or owners can use the subgroup.

LedgerFullGroup < LedgerSubgroup

Besides being a grouping object, this one also keeps track of LedgerUsers in various roles (plain member, moderator and so on), linked to it by LinkRole records. The plain LedgerSubgroup objects controlled by this group will delegate their permission determination to this LedgerFullGroup and its membership lists by a LinkGroupRoleDelegation record for each subgroup.

Currently, extra settings (post moderation options, etc) are stored in a related GroupSettings database table, though we may need to use ledger objects if a history of changes needs to be preserved.

LedgerAwardCeremony < LedgerBase

This record marks the time when (usually weekly) rating points are awarded. We perform the ceremony relative to planetary time, so the actual time between ceremonies won’t always be exactly 7 days and 0 seconds, due to the slowdown of the earth’s rotation and server downtime problems. If you delete this record, the ceremony is ignored and just about everything needs to be recalculated, including award numbers for subsequent ceremonies and LinkBase records. Currently deleting this kind of record is not implemented, so don’t do it.

Calculating Awards

To avoid rewriting the whole database, current point levels based on fading and awards over time for regular LedgerBase objects are calculated on demand and cached. We also have a recalculate all cached values feature, in case you import records from somewhere else or didn’t save cached values. It’s useful for test purposes to compare the cached values calculated both ways.

There’s a bit of extra complication when Link records have been (un)deleted or (un)approved where the points given to the child disappear and reappear (creators are always charged for the points spent). We generally calculate based on the final deleted state for the latest award ceremony. Though you may want to calculate the state of the system as of a particular date in the past.

The LedgerAwardCeremony class has functions for keeping track of the latest award ceremony and starting a new one (invoked by a weekly cron script on the web server or manually).

For the full update basically replay everything. Process both Link and Ledger records in combined chronological order. Reset all cached things like delete and approved flags and current rating points to default values as the records are processed. Add changes from Link records to the appropriate cached value in Ledger records. Ignore records in the future when doing queries to find group membership etc. Probably won’t implement the full update unless needed.

For the incremental update, whenever a Link record is being applied to a Ledger record, first check if the Ledger record is from an earlier ceremony. If so, fade the cached points by the fade factor raised to the number of ceremonies done since the record was last updated. Then add/subtract points from the appropriate cached Ledger values. Generally Link child will have them added to the appropriate up/meh/down cached value, if the link is approved and not deleted. The Link creator has the points spent taken off their current “up” count or added to their “down” count (which could be a sign of a spending more points than you should have bug), even for deleted or unapproved links.

For weekly award ceremonies, all User records need to have their entitled points calculated (based on the various bonuses they are entitled to) and added to the cached points (after the old cached points are faded). As well the remaining entitled points counter for that user is reset (so they can spend a few points that week even if they are overall negative). Though we may want to garnish perhaps half of remaining entitled points to improve their up rating if they are overall negative (less voice for suppressed people).

Bonuses are kept track of by linking the user to various categories, possibly multiple times. There’s a category for +1 weekly entitlement point for a good year of posting, another one for -1 weekly point of annual inactivity, and one for 0 entitlement change for just doing some posting during the year. Those bonus links are made during the award ceremony on the anniversary of a user’s account activation.

LedgerBirthday < LedgerBase

Identifies the birthday of a user. Mostly an excuse to reuse the generic timestamp field that LedgerAwardCeremony made necessary.

LedgerDeathday < LedgerBase

Identifies the date of a user’s death.

LinkBase Classes

This is the base class for links of all kinds between pairs of objects. There is one database table for all instances of this class and subclasses. The particular subclass identifies what the link’s purpose is (object A owns object B, object A is a list header and B is in the list, etc).

LinkOwner < LinkBase

The parent is a LedgerUser who is to be treated almost the same as the creator of the object specified as the child of the link. They can do everything the creator can do, except change the creator. This record is also used to link the object to the creator if the creator doesn’t spend points on the object via some other LinkBase record (such as a LinkReply or LinkGroupContent). That’s because the implied LinkCreator (the creator_id field in the LedgerBase record) has zero reputation points assigned to it. If we just created the object, at the next awards ceremony it would have zero points and be garbage collected. Also by using a LinkOwner, rather than having an implied LinkCreator with a fixed 1.0 fee, we can charge a larger and variable fee for more resource consuming objects, such as big media files.

Only the creator or an existing owner can approve new owners of an object.

Adding an owner to a LedgerUser currently doesn’t do much, but in future it could be a way of letting multiple people control a person or company account.

LinkReply < LinkBase

The child is a reply to the parent. Usually both objects are LedgerPost, or at least LedgerContent.

LinkMedia < LinkBase

The child is some sort of media which should be displayed when the parent is shown. Usually the parent is a LedgerPost and the child is a LedgerMedia of some type. We just treat them all as pictures and show them in a grid after the text of the post (grid order determined by the order of datestamps of these link records). The user can click on individual ones to play them.

LinkHomeContent < LinkBase

Parent is a Ledger(Full/Sub)group or LedgerThing and child is usually a LedgerPost. That post will be used as the home post (home page, status page, profile page, description, etc) for the parent. If there are several, we show them all, kind of like a list of announcements or pinned posts. Delete the LinkHomeContent to unpin them.

LinkHomeGroup < LinkBase

Parent is a LedgerUser and child is a LedgerFullGroup. That group will be used as the home page for the user, combined with some user related information (like a list of all their posts, time of last logon, etc). Normally the group will be created when the user is created, with the user listed as the group’s creator. That way they’ll be able to control who can see their personal posts. If someone wants to follow that user, they will simply subscribe to the group as a reader. If there are several records, only the latest one will be used.

LinkRole < LinkBase

Specifies the role a person has in a group. Parent is the LedgerFullGroup, child is the LedgerUser.

LinkGroupRoleDelegation < LinkBase

Specifies that the parent LedgerFullGroup is used for permission checks for users trying to access the child LedgerSubgroup. If there is more than one of these records, they’re all checked until one of them gives permission or all of them deny permission.

LinkGroupContent < LinkBase

Links a child LedgerContent (usually a LedgerPost or LedgerMedia) to a parent LedgerFullGroup or LedgerSubgroup. Means that the child is considered part of the group’s displayable content and will use the group’s permissions to decide who can see the child. Quite often the links will be pending until each end is approved by a moderator or higher role (usually moderator at the group end, owner/creator at the post end). Note that all replies to a post that are in the same group also need a LinkGroupContent record, since group inclusion can change from post to post as the users wish (user interface defaults to using the same groups as the original post when replying).

Unlike most Links where only the owner or creator of the linked to thing can change the approval, group message moderators can change the approval of the parent (group) end of this link (since it’s trying to connect to their group). As well, they can also delete this link (to get it out of their pending things to moderate list).

LinkSubgroup < LinkBase

Specifies that the child LedgerSubgroup or LedgerFullGroup is subordinate to the parent LedgerFullGroup or LedgerSubgroup. Used for making a directed graph structure of groups in order to classify things (like photos or posts attached to individual group objects) or otherwise break them down into more managable smaller chunks. Multiple parents are allowed and you can make cycles if you wish.

LinkRelationship < LinkBase

This is the abstract base class to show the parent and child are LedgerUser objects in some sort of relationship, such as parent, child, spouse, friend, enemy, employer, follower.

Only the creator and people who need to give approval can delete it.

Of course, the link will fade away over time unless new reputation points are spent to create a fresh copy. Thus you may have several copies of the same relationship in the database. Whichever one is undeleted and has full approvals will be used to find people with the relationship.

All ends of the link which are the same as the creator are automatically approved. Other ends will be in a pending state until the referred person uses a LedgerApprove to approve their end. Where you can approve, you can also unapprove your end of the relationship. Until both are approved, it’s a partial relationship (admirer rather than friends) and not usually shown to users or used for finding related people.

When finding posts for a person’s feed, items from related people will be given increased priority.

LinkSymmetricRelationship < LinkRelationship

Relationships can be symmetric (friend/friend) or asymetric (parent/child). This subclass handles the symmetric ones. For searching efficiency, we’ll just arbitrarily make the older user (lower ID number) the parent_id in symmetric links.

Subclasses of this will export a function that finds all people related to a specified bunch of people, such as Friends(X). Note the input X is a collection of people, so we can use the results of a previous relationship test as input. That way Enemies(Enemies(X)) works, if you want to give permissions to enemies of X’s enemies.

LinkAsymmetricRelationship < LinkRelationship

Asymmetric relationships have distinctly different ends of the relationship (such as real world parent and child, or Follower/Leader, Employer/Employee). The particular relationship class will define which one goes in the parent field and which is the child.

Subclasses will export two functions that find all people that are related to a specified person, one for each end, so you can ask for all employees of a boss via Employees(x) or ask for all bosses of an employee via Bosses(x).

LinkOneWayRelationship < LinkAsymmetricRelationship

Rather than two way relationships, some are one way such as follower. The person being followed (a Leader) doesn’t have a say in whether they’re being followed. Thus approval is always given for the child end and the relationship is set up with the originator as parent (follower in the parent field, followed or leader in the child field). If the parent is the creator, they’re automatically approved. Only the creator and parent can delete this link.

LinkFriend < LinkSymmetricRelationship

The parent is a friend of child. This is a bidirectional relationship so both ends are friends with each other. If one end hasn’t approved the friendship, then we’ll refer to it as an unacknowledged friendship and perhaps say X is admired by Y and Y has an admirer. If neither are approved, then it’s a suggested (by the creator) friendship.

LinkEnemy < LinkSymmetricRelationship

You can declare your enemies too, if they agree. Bidirectional like LinkFriend. Real enemies will give and accept negative points. Don’t expect that to happen often.

LinkSpouse < LinkSymmetricRelationship

Parent is a spouse of child. Bidirectional. Both ends need to be approved.

LinkParentChild < LinkAsymmetricRelationship

Parent is parent in real life of child. Both ends need to be approved.

LinkEmployerEmployee < LinkAsymmetricRelationship

Parent is the employer or company employing the child. Both ends need to be approved. Otherwise you’ll see something like “claims to be employed by X”.

LinkFollower < LinkOneWayRelationship

Parent LedgerUser is a fan or at least interested in the child. If you like the child LedgerUser, send some positive points, negative if you hate them. They receive those points either way.

LinkOpinion < LinkBase

Parent LedgerUser (usually the creator) has an opinion about the child object (usually a LedgerContent or a LedgerUser or LedgerThing). Can be positive or negative or meh, decided by the points awarded to the child. Usually no points awarded to the parent, unless a third party is essentially paying someone to have an opinion. Automatically approved for the child end, parent end auto approved if it is the creator, and can only be deleted or unapproved by the creator or parent end.

For example, if you like a post, you can make a LinkOpinion referencing the post (as the child object) and give it some of your rating points. The parent field would identify you as the person with the opinion.

A meta example: if you don’t like the rating someone made on a post, you can make a LinkOpinion that has an AuxLink record referencing the rating (which is itself another LinkOpinion) and give some opposite points to the post (specified in the child field) to counter the bad rating. Or you could specify the person with the bad opinion as the child and rate them.

Or if you like the way someone connected a bit of useful information (an existing old post answering a frequently asked question) as a reply to a new post, you can reward them for making that link with a LinkOpinion’s AuxLink referencing the LinkReply record and with the creator of the LinkReply as the rewarded child.

LinkMetaMod < LinkOpinion

This is an opinion about someone else’s opinions, used for meta-moderation. The main difference is that it is anonymous (the parent is hidden to most people). Usually members of a group will on occasion (much more often if they have the meta-moderator role in the group) be shown a random opinion about a message in the group (usually a recent one, but not always). They can decide how many points to award and in which direction to the person making the opinion. Additionally they will receive a bit of a positive reward too, and for convenience the LinkMetaMod will be initially approved on the parent end rather than needing the creator to be the parent for pre-approval. The group will pay for the points and be listed as the creator of the LinkMetaMod record.

For meta-meta-moderation, we’ll just use an annual performance review for the more significant meta-moderators (since they get paid in points, they could do this as a kind of job, and moderators would also get points from the meta-moderator operation, and the group could get points by having a weekly membership fee).

Auxiliary Classes

This database table and class is used for connecting LedgerBase records (a LedgerDelete/Undelete or LedgerApprove/Unapprove) and LinkBase records. Used for deleting, undeleting, and giving approvals to a LinkBase record. The main difference with LinkBase is that no rating points are involved.

AuxLedger

Like AuxLink, but for connecting LedgerBase objects (usually a LedgerDelete/Undelete) to child LedgerBase objects (things being deleted).

Generating the Database

Here are the rails command lines and edits needed to generate the database:

rails generate model LedgerBase type:string
bool1:boolean date1:datetime number1:integer string1:string string2:string
text1:text creator:references original:references amended:references
deleted:boolean current_down_points:float current_meh_points:float
current_up_points:float

rails generate model LinkBase type:string
parent:references child:references creator:references deleted:boolean
pending:boolean rating_points_spent:float rating_points_boost_child:float
rating_points_boost_parent:float rating_direction:string award_number:integer

rails generate model AuxLedger parent:references child:references

rails generate model AuxLink parent:references child:references

# And for subclasses, do something like this:

rails generate model LedgerList --parent=LedgerBase

Combine and edit the migration files to have default values and better field details like this:

class CreateLedgerAndLinkBases < ActiveRecord::Migration[6.0]
  def change
    create_table :ledger_bases, force: false, comment: "Ledger objects base class and record.  Don't force: cascade deletes since this is a write-once ledger where usually nothing gets deleted (except during points expired object garbage collection)." do |t|
      t.string :type, default: "LedgerBase", comment: "Names the ActiveRecord subclass used to load this row, turning on single table inheritance."
      t.references :original, null: true, foreign_key: {to_table: :ledger_bases, name: "fk_rails_ledgeroriginal"}, comment: "Points to the original version of this record, or equal to id if this is the original one.  NULL if not initialised (should be a copy of the id of this record)."
      t.references :amended, null: true, foreign_key: {to_table: :ledger_bases, name: "fk_rails_ledgeramended"}, comment: "Points to the latest version of this record, or NULL if this is not the original record."
      t.boolean :deleted, default: false, comment: "True if there is a LedgerDelete record that is currently deleting this record, otherwise false (record is alive)."
      t.boolean :has_owners, default: false, comment: "True if there is one or more LinkOwner records (even deleted ones) that references this record.  False if there are none, which means we can skip searching for LinkOwner records every time we check permissions, which saves a lot of database queries!"
      t.boolean :is_latest_version, default: true, comment: "True if the record is the latest version of the object.  False otherwise.  Caches the result of looking up the original object and seeing which record is the latest, so we have less overhead when displaying only the latest versions in a list of posts.  Also lets us skip older versions directly in an SQL query."
      t.references :creator, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_ledgercreator"}, comment: "Identifies the user who created this record, using their original ID."
      t.boolean :bool1, default: false, comment: "Generic boolean, defined by subclasses."
      t.bigint :number1, default: 0, comment: "Generic number for counting things, or referencing other database tables, usage defined by subclasses."
      t.string :string1, default: "", comment: "Generic string (up to 255 bytes), defined by subclasses."
      t.string :string2, default: "", comment: "Generic string (up to 255 bytes), defined by subclasses."
      t.text :text1, default: "", comment: "Generic text (lots of characters), defined by subclasses."
      t.datetime :date1, null: true, comment: "Generic date and time from year 0 to year 9999, defined by subclasses."
      t.float :current_down_points, default: 0.0, comment: "Number of rating points in the down direction for this object. This is the current total, including fading over time (recalculated at the beginning of the week in the awards ceremony) plus new ratings applied this week."
      t.float :current_meh_points, default: 0.0, comment: "Number of rating points in the meh non-direction for this object. This is the current total, including fading over time (recalculated at the beginning of the week in the awards ceremony) plus new ratings applied this week."
      t.float :current_up_points, default: 0.0, comment: "Number of rating points in the up direction for this object. This is the current total, including fading over time (recalculated at the beginning of the week in the awards ceremony) plus new ratings applied this week."
      t.integer :current_ceremony, default: -1, comment: "The number of the awards ceremony that the current points were calculated for.  0 means before the first ceremony.  Set to -1 if needs recalculation based on date created."
      t.timestamps
    end

    add_index :ledger_bases, :string1
    add_index :ledger_bases, :string2
    add_index :ledger_bases, :number1

    create_table :link_bases, force: false, comment: "LinkBase base class and record for linking LedgerObjects together." do |t|
      t.string :type, default: "LinkBase", comment: "Names the ActiveRecord subclass used to load this row, turning on single table inheritance."
      t.references :parent, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_linkparent"}, comment: "Points to the parent LedgerBase object (or subclass) which is usually the main one or older one in the association.  Uses the original ID of the parent."
      t.references :child, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_linkchild"}, comment: "Points to the child LedgerBase object (or subclass) which is the child in the association.  Uses the original ID of the child."
      t.references :creator, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_linkcreator"}, comment: "Identifies the User who created this link, using their original ID."
      t.bigint :number1, default: 0, comment: "Generic number for counting things, or referencing other database tables, usage defined by subclasses."
      t.string :string1, default: "", comment: "Generic string (up to 255 bytes), defined by subclasses."
      t.boolean :deleted, default: false, comment: "True if there is a LedgerDelete record that deletes this record, otherwise false (this record is alive)."
      t.boolean :approved_parent, default: false, comment: "True if the link to the parent object has been approved.  False means it's pending; the link record exists but it can't be traversed (sort of like being deleted) until someone gives permission via LedgerApproved."
      t.boolean :approved_child, default: false, comment: "True if the link to the child object has been approved.  False means it's pending; the link record exists but it can't be traversed (sort of like being deleted) until someone gives permission via LedgerApproved."
      t.float :rating_points_spent, default: 0.0, comment: "The number of points spent on making this link by the creator."
      t.float :rating_points_boost_child, default: 0.0, comment: "The number of points used to boost the rating of the child object."
      t.float :rating_points_boost_parent, default: 0.0, comment: "The number of points used to boost the rating of the parent object."
      t.string :rating_direction, default: "M", comment: "Use U for up, D for down or M for meh."
      t.integer :rating_ceremony, default: 0, comment: "The week's award ceremony number when this record was created, 0 if before time starts."
      t.timestamps
    end

    add_index :link_bases, :string1
    add_index :link_bases, :number1

    create_table :aux_links, force: false, comment: "AuxLink class and record for connecting LedgerObjects (usually LedgerDelete) to LinkBase records (usually links being deleted)." do |t|
      t.references :parent, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_auxlinkparent"}, comment: "Points to the LedgerBase object (or subclass) which has the delete or undelete or give permission order."
      t.references :child, null: false, foreign_key: {to_table: :link_bases, name: "fk_rails_auxlinkchild"}, comment: "Points to the child LinkBase object (or subclass) which is being modified by the parent."
    end

    create_table :aux_ledgers, force: false, comment: "AuxLedger class and record for connecting LedgerBase records (usually LedgerDelete) to other LedgerBase records (usually objects being deleted)." do |t|
      t.references :parent, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_auxledgerparent"}, comment: "Points to the LedgerBase object (or subclass) which has the delete or undelete order."
      t.references :child, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_auxledgerchild"}, comment: "Points to the child LedgerBase object (or subclass) which is being modified by the parent."
    end

    create_table :group_settings, comment: "Extra information for each LedgerFullGroup object is stored in this table." do |t|
      t.references :ledger_full_group, null: false, foreign_key: {to_table: :ledger_bases, name: "fk_rails_groupsettingsgroup"}, comment: "Points to the LedgerFullGroup (a LedgerBase subclass) that this record contains the extra settings for."
      t.boolean :auto_approve_non_member_posts, default: false, comment: "True if posts attached to the group by a non-member are automatically approved (if they have enough points).  False means a moderator will need to approve them."
      t.boolean :auto_approve_member_posts, default: true, comment: "True if posts attached to the group by a member are automatically approved (if they have enough points).  False means a moderator will need to approve them.  Weird if you only auto-approve non-members."
      t.boolean :auto_approve_members, default: false, comment: "True if new member applications are automatically approved (if they have enough points).  False requires approval by a member moderator."
      t.float :min_points_non_member_post, default: -1.0, comment: "Minimum number of net rating points needed when posting as a non-member to get automatic approval.  If they don't have enough, their post will need to be approved by a moderator.  Ignored if automatic approval is turned off."
      t.float :max_points_non_member_post, default: 2.0, comment: "Maximum number of net rating points allowed when posting as a non-member to get automatic approval.  If they have too many, their post will need to be approved by a moderator.  Ignored if automatic approval is turned off."
      t.float :min_points_member_post, default: -10.0, comment: "Minimum number of net rating points needed when posting as a member to get automatic approval.  If they don't have enough, their post will need to be approved by a moderator.  Ignored if automatic approval is turned off."
      t.float :max_points_member_post, default: 10.0, comment: "Maximum number of net rating points allowed when posting as a member to get automatic approval.  If they have too many, their post will need to be approved by a moderator.  Ignored if automatic approval is turned off."
      t.float :min_points_membership, default: 1.0, comment: "Minimum number of net rating points needed when applying to be a member to get automatic approval.  Ignored if automatic approval is turned off.  If they don't get automatically approved, a moderator will need to approve their request."
      t.float :max_points_membership, default: 100.0, comment: "Maximum number of net rating points allowed when applying to be a member to get automatic approval.  Ignored if automatic approval is turned off.  If they don't get automatically approved, a moderator will need to approve their request."
      t.string :wildcard_role_banned, default: "", comment: "Wildcard expression to identify people who are banned, in addition to explicit LinkRole records.  So we can ban friends of banned people, etc."
      t.string :wildcard_role_reader, default: "", comment: "Relationship expression specifying additional people who are allowed to read the group."
      t.string :wildcard_role_member, default: "", comment: "Relationship expression specifying additional people who are considered members of the group, even if they didn't apply."
      t.string :wildcard_role_message_moderator, default: "", comment: "Relationship expression specifying additional people who are allowed to moderate (approve/delete) messages."
      t.string :wildcard_role_meta_moderator, default: "", comment: "Relationship expression specifying additional people who are allowed to anonymously rate opinions about messages in this group."
      t.string :wildcard_role_member_moderator, default: "", comment: "Relationship expression specifying additional people who are allowed to moderate membership requests."
    end
  end
end

Flesh out ledger_base.rb and link_base.rb with a few associations too:

class LedgerBase < ApplicationRecord
  # Always have a creator, but "optional: false" makes it reload the creator
  # object every time we do something with an object.  So just require it to
  # be non-NULL in the database definition.
  belongs_to :creator, class_name: :LedgerBase, optional: true

  belongs_to :original, class_name: :LedgerBase, optional: true
  belongs_to :amended, class_name: :LedgerBase, optional: true

  has_many :link_downs, class_name: :LinkBase, foreign_key: :parent_id
  has_many :descendants, through: :link_downs, source: :child
  has_many :link_ups, class_name: :LinkBase, foreign_key: :child_id
  has_many :ancestors, through: :link_ups, source: :parent

  has_many :aux_ledger_downs, class_name: :AuxLedger, foreign_key: :parent_id
  has_many :aux_ledger_descendants, through: :aux_ledger_downs, source: :child
  has_many :aux_ledger_ups, class_name: :AuxLedger, foreign_key: :child_id
  has_many :aux_ledger_ancestors, through: :aux_ledger_ups, source: :parent
  has_many :aux_link_downs, class_name: :AuxLink, foreign_key: :parent_id
  has_many :aux_link_descendants, through: :aux_link_downs, source: :child
end

class LinkBase < ApplicationRecord
  belongs_to :parent, class_name: :LedgerBase, optional: false
  belongs_to :child, class_name: :LedgerBase, optional: false
  belongs_to :creator, class_name: :LedgerBase, optional: false

  has_many :aux_link_ups, class_name: :AuxLink, foreign_key: :child_id
  has_many :aux_link_ancestors, through: :aux_link_ups, source: :parent
end

Then do rails db:seed to set up the initial database contents. One key step is making the root LedgerBase object, with itself as its creator.

ActiveRecord::Base.connection.execute(
  "INSERT into ledger_bases
    (id, string1, string2, text1, creator_id, created_at, updated_at)
  VALUES
    (0, 'Root LedgerBase Object', 'agmsmith@ncf.ca', 'The special root object
    which we need to manually create with a creator id of itself.  Then initial
    system objects can be created with it as their creator.  AGMS20200206', 0,
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);"
)

Test Plan - A few Use Cases

A new user writes a message. Another user replies to it. First user edits their message. What does second user see? First user replies to second user’s message. First user deletes their original message.

A user adds some photos and videos to a message. They are separate objects, and need the expendature of rating points to be attached (more points are required for a larger number of bytes). What happens when they run out of points? Do they get attached to the message and show up when it is displayed?

First user sets up a small category tree and adds a message to a couple of categories. Second user navigates tree to see messages about their favourite topic.

Weekly batch processing adds some reputation points to first and second user. First user likes second user, awarding some up points, declaring their appreciation. Second user awards some down points to first user, declaring their disgust. What are their total points? Another weekly batch run happens. What are their points?

Change Log

$Header: /var/www/ratingstone.agmsmith.ca/docs/RCS/Database\040Design.md,v 1.56 2021/01/16 17:25:05 agmsmith Exp $

$Log: Database\040Design.md,v $
Revision 1.56  2021/01/16 17:25:05  agmsmith
Typo.

Revision 1.55  2021/01/10 22:42:02  agmsmith
Clarify current_ceremony negative numbers.

Revision 1.54  2021/01/10 20:33:51  agmsmith
More award ceremony clarification.

Revision 1.53  2021/01/08 23:00:03  agmsmith
Explain award ceremony.

Revision 1.52  2021/01/08 18:41:24  agmsmith
Added more award ceremony fields; starting to implement them.

Revision 1.51  2021/01/04 17:22:42  agmsmith
Added LedgerThing and some LinkOpinion clarification.

Revision 1.50  2020/09/20 23:45:52  agmsmith
Need to have a subject for posts, add a string for generic content classes.

Revision 1.49  2020/09/13 20:48:36  agmsmith
Add note to is_latest_version as being useful in SQL queries too.

Revision 1.48  2020/09/11 01:26:12  agmsmith
Make the default of is_latest_version true, since new records are usually
latest.

Revision 1.47  2020/09/10 21:58:31  agmsmith
Add change log to index.

Revision 1.46  2020/09/10 21:57:02  agmsmith
Add is_latest_version field, to optimise version checking.

Revision 1.45  2020/08/06 22:53:07  agmsmith
Update database migration example with current version.

Revision 1.44  2020/08/05 20:42:14  agmsmith
Added has_owners field to LedgerBase.

Revision 1.43  2020/08/04 21:18:02  agmsmith
Add LinkHomeGroup, for user's home page.

Revision 1.42  2020/08/02 20:46:02  agmsmith
Move class structure retrospective regrets to later in the introduction.

Revision 1.41  2020/07/15 20:46:09  agmsmith
amended_id now used in later versions to detect race conditions.

Revision 1.40  2020/07/15 01:21:50  agmsmith
Add a note about multipurpose class structure maybe better than separate object
and links.

Revision 1.39  2020/07/14 00:22:05  agmsmith
Better wording.

Revision 1.38  2020/07/14 00:17:38  agmsmith
Group message moderators have special powers over LinkGroupContent that links
something to their group.

Revision 1.37  2020/07/12 01:00:19  agmsmith
Both ends of the link need to be approved before points awarded.

Revision 1.36  2020/07/10 19:35:35  agmsmith
Mention that the original version's id number is stored in various records.

Revision 1.35  2020/07/07 21:35:52  agmsmith
Put some of the exploratory explanations near the beginning, leaving the class
definitions at the end.

Revision 1.34  2020/07/07 20:51:57  agmsmith
Added meta-moderation idea from a discussion on Slashdot.

Revision 1.33  2020/07/04 16:58:42  agmsmith
No GroupSettings_id field needed, separate table indexed by id of the group
instead.

Revision 1.32  2020/06/30 20:51:44  agmsmith
Added database setup for GroupSettings table.

Revision 1.31  2020/06/30 00:24:46  agmsmith
Proof-reading pass.  Fix conceptual problem - no owners of links.  Also clarify
who can delete links (if you need approval, you can delete it too).

Revision 1.30  2020/06/29 20:27:42  agmsmith
Nope, not owners, else you could get someone to preapprove a relationship.

Revision 1.29  2020/06/29 15:43:14  agmsmith
Work out Relationship class structure, write up a bit more about relationships
and the dimensions of symmetry and approval.

Revision 1.28  2020/06/29 01:01:00  agmsmith
Added relationships section.

Revision 1.27  2020/06/27 21:30:08  agmsmith
Proof reading.  Added Subgroups.  Wildcard language.

Revision 1.26  2020/06/26 00:37:21  agmsmith
Added LinkRelationship, removed LinkPermission.

Revision 1.25  2020/06/25 00:06:50  agmsmith
Decide on making ungrouped posts public.  Add LinkOwner record.

Revision 1.24  2020/06/24 11:18:49  agmsmith
More work in progress on permission roles and discussion groups.

Revision 1.23  2020/06/21 00:20:58  agmsmith
Working on the Permissions Model.  Idea of Groups for all posts, roles.

Revision 1.22  2020/06/20 17:14:23  agmsmith
Rework approval vs deletion of records.  Now have separate child and parent end
of the link approval flags.  Delete is orthogonal to approval.

Revision 1.21  2020/06/15 15:56:25  agmsmith
Rewording, also combine pending and deleted functionality into the same thing
(granting permission undeletes a record).

Revision 1.20  2020/04/10 15:58:33  agmsmith
Have original_id be NULL on creation, but soon changing to copy of id.

Revision 1.19  2020/04/10 15:40:04  agmsmith
Update migration comment for original_id.

Revision 1.18  2020/04/10 15:37:19  agmsmith
Change original_id to be equal to id for the original record, not NULL.

Revision 1.17  2020/03/28 16:19:43  agmsmith
Split out AuxLink and AuxLedger records, making things simpler again.

Revision 1.16  2020/03/28 13:18:21  agmsmith
Typos.

Revision 1.15  2020/03/27 19:03:58  agmsmith
Minor typos fixed.

Revision 1.14  2020/03/26 20:25:45  agmsmith
Rethought deleting and permissions.

Revision 1.13  2020/02/20 21:45:08  agmsmith
Fix typos after proofreading.

Revision 1.12  2020/02/07 15:28:32  agmsmith
Mention initialising the root object in the database.

Revision 1.11  2020/02/04 22:20:21  agmsmith
Proof reading pass done.  Also added list design choices.

Revision 1.10  2020/02/03 22:33:40  agmsmith
Fix up formatting in kramdown for preformatted code.

Revision 1.9  2020/02/03 22:26:20  agmsmith
Added command lines needed to generate the database.

Revision 1.8  2020/02/02 22:15:24  agmsmith
Added permissions records.

Revision 1.7  2020/02/02 21:44:52  agmsmith
After some experiments, decided to use floating point rating points.

Revision 1.6  2020/01/31 21:54:25  agmsmith
Work in progress adding fields and descriptions, worrying about deleting.

Revision 1.5  2020/01/30 22:16:28  agmsmith
Starting to add fields to the base object record.

Revision 1.4  2020/01/30 15:45:39  agmsmith
Explain the ledger write-once system and single table inheritance.

Revision 1.3  2020/01/30 14:40:09  agmsmith
Writing continues, work in progress.

Revision 1.2  2020/01/29 22:52:13  agmsmith
Fixed up formatting of RCS log and version number.

Revision 1.1  2020/01/29 22:43:07  agmsmith
Initial revision