$Id: Database\040Design.md,v 1.95 2023/09/13 00:47:03 agmsmith Exp $
It’s a social media and reputation system prototype web site that lets users write posts and rate things. There’s a bit of an economy of rating points, with new ones being doled out weekly and old points fading away over time. Points are also used for actions on the system (such as replying to a post) to deter unthinking spamming and other abuses. Anyway, we need a database to keep track of the users, posts, rating points, groups of users and so on.
Because I’m thinking of 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 internal to the objects so that an object can have multiple parents (useful for filing something under multiple categories).
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 someone wants to see 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 how the text changed over time.
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, marking the original link record as deleted.
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.
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.
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 method. 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.
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.
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. Though having simpler link records makes the database smaller and faster.
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 over time.
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.
We may want to identify people based on relationships. Or maybe not - relationships are currently unimplemented, and for “feed” purposes we can “follow” a user by simply becoming a member of their personal group. The obvious relationship 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.
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.
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 because they created the link).
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.
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 in that group.
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, unless we want to use JSON in the group record):
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.
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.
Used for marking possibly multiple objects and/or links as deleted, undeleted, approved or rejected. AuxLedger and AuxLink records are used to associate this LedgerChangeMarking with all the things (both LedgerBase object records and LinkBase link records) being changed. Subclasses specify which kind of marking (mark deleted, mark approved, etc) to do. A boolean in the record specifies whether to set or clear the marking.
Please don’t make loops of LedgerChangeMarking records, this will lock up the server. While you can do an undelete by deleting a LedgerDelete with another LedgerDelete record, the recommended way (easier for the user to understand) to undo a delete is to make a new LedgerDelete which clears the delete flag on the same set of files as the original LedgerDelete.
While you can have several LedgerChangeMarking records affecting the same object, only the undeleted ones (can be expired, that doesn’t matter) played back in chronological order decide the target object’s status. So yes, if enough time passes, the LedgerChangeMarking will fade away and the targets will no longer be deleted or approved etc. Though most often the targets will have faded away too by that time. Or we could have a policy of not garbage collecting LedgerChangeMarking records.
Deletes the specified records, can be LedgerBase and/or LinkBase ones. If the new_marking_state flag is false, it undeletes.
You usually need to be the creator or owner of the link to change the deleted state. I was thinking that people who can approve the link ends could also delete it, but that then means that someone who has been banned from a group could delete their banning link. May need to revisit this, with exceptions depending on the link subclass.
Both child and parent approvals of the specified LinkBase records are potentially changed when you make a LedgerApprove request. The actual changes are determined by whether you have permission to change those approvals (relative security role used). To more strongly reject permission, delete the link instead (if you are allowed to).
Idenfities a particular user. We don’t have placeholder users (for celebrities and other external people); a group should be created by fans instead.
Elsewhere in the User table, we keep track of non-ledger (no version history) user information:
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. I prefer the latter method.
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 LinkHomePost. 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”. Probably not in the first prototype, just posts is fine.
A post made by a user. This is the object the user interacts with the most. Contains a subject and 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).
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.
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.
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 LinkHomePost) 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.
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, or JSON in the group record if we want to keep it all in one place.
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. Currently deleting this kind of record may cause problems (like duplicate ceremony numbers), so don’t do that.
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. If needed, we can recalculate everything for a particular object back to the beginning of time, which is useful when you import records from somewhere else or didn’t save cached values in a backup.
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).
Bonuses are kept track of by linking the user with LinkBonus records to various things (category or a post explaining the bonus), possibly multiple times for some or singletons for others (like e-mail activation). There’s a category for +1 weekly entitlement point for a good year of posting, another one for -1 weekly point of annual inactivity, or no bonus for just doing a minor amount of posting during the year. Those bonus links are made during the award ceremony on the annual anniversary of a user’s account activation.
As mentioned elsewhere, the weekly allowance bonus points are added to the user’s meh score each week (theoretically just after the ceremony is done). The user can spend meh points and if their up is more than their down points, the extra up points can be spent too, though their meh will be used first. In the future if users are particularly obnoxious, we can also limit the spending of meh points to ones above the down points, but then we’d need another mechanism to make sure they can still spend their allowance points in all situations.
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).
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.
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.
The child is a reply to the parent. Usually both objects are LedgerPost. Besides the usual creator, the users who are allowed to approve either end of the link are also allowed to delete the link. So if someone attaches their post as a reply to your post, you can break that connection. Quotes are also done with LinkReply, but child and parent are reversed (you can tell by the backwards creation dates).
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.
Parent is a Ledger(Full/Sub)group or LedgerThing and child is 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 LinkHomePost to unpin them.
Parent is a LedgerUser and child is a LedgerFullGroup. That group will be used as the home group for the user’s personal posts. 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.
Specifies the role a person has in a group. Parent is the LedgerFullGroup, child is the LedgerUser.
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.
Links a child LedgerPost to a parent LedgerFullGroup or LedgerSubgroup. Means that the child is considered part of the group’s collection of posts 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). Similarly the users who can change the approval of the child end of the link (usually the creator of the post) are allowed to delete the link.
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 (though that will reduce database query performance).
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. Might be used in the future, but not in the initial minimal prototype system.
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.
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.
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).
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.
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.
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.
Parent record (a LedgerUser) is a spouse of the child record (also a LedgerUser). Bidirectional. Both ends need to be approved.
Parent is parent in real life of child. Both ends need to be approved.
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”.
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.
The link creator has an opinion about the child object (usually a LedgerPost or a LedgerUser or LedgerThing). Can be positive or negative or meh, decided by the points awarded to the child. Parent is usually the creator of the child, in case the creator wants to award them some spendable points too. Automatically approved for the child end and parent end, and can only be deleted or unapproved by the creator.
For example, if you like a post, you can make a LinkOpinion referencing the post (as the child object) and rate it upwards with your points. The parent field would identify the post’s author, and give them some additional points if you wish to essentially rate them too. The default for a quick “like” is to just use half the points on the child, half on the parent. A fancier user interface would let you specify parent points separately, with possibly a separate direction.
You can have an opinion about someone else’s opinion or other link objects. If you disagree with the rating someone made on a post, you can make a LinkMetaOpinion that has the person or group responsible for the meta opinion as the child and the author of the original opinion as the parent, and the LinkOpinion record ID in the number1 field. The creator is the user making the meta-opinion (usually a moderator for the group if child is a group) and paying for it. It doesn’t affect the original opinion’s operation, which doesn’t have a reputation rating of its own.
Your meta opinion doesn’t have to always be about some other opinion. 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 LinkMetaOpinion’s referencing the LinkReply record and with the creator of the LinkReply as the parent. I suppose you could have an opinion about a LinkMetaOpinion too.
This kind of link adds or removes weekly bonus points to/from a child LedgerUser record. The parent object explains the bonus, usually in the form of LedgerPost of some sort that can be displayed to the user if they want to know why they got that bonus, or perhaps a Category object (have a tree of bonuses). Normal users can’t create this kind of link other than indirectly through their actions.
The links fade away as usual and the bonus can have a time limit as well. Your bonus for being a good poster in the previous year can be set to last for a year and then stop contribuing when it hits its expiry ceremony number. Though if the expiry date is too far in the future, it will also fade away as usual and eventually get garbage collected. Similarly you’ll have to reenter an e-mail address once the bonus for having a tested e-mail has expired. Some bonuses like the e-mail bonus will be limited to only one existing at a time.
The total of all bonus points at the award ceremony is pegged between 0 and 100 (in case they have too many negative bonuses, or have unfairly many positive bonuses) and becomes their week’s allowance. The user also gets to spend that many points even if their net reputation is negative, to avoid totally silencing them. The weekly allowance and week’s spending is stored in the User record, outside the ledger system, mostly for informational and user interface purposes.
A subclass for bonuses which are unique. There can only be one of these active at the same time linked to the same parent object. For example, the activated e-mail bonus has to be unique, otherwise the user will just keep on activating more e-mail addresses to get more of a bonus.
This database table and class is used for connecting LedgerBase records (a LedgerChangeMarking usually) and LinkBase records. Used for deleting & undeleting, and only for LinkBase subclasses - giving approvals. The main difference with LinkBase is that no rating points are involved.
Like AuxLink, but for connecting LedgerBase objects (usually a LedgerChangeMarking object) to child LedgerBase objects (such as things being deleted).
There are several permissions used for objects and links to control who can do what, the last three are for links only:
This is the default for classes inheriting from LinkBase.
Here are the rails command lines (out of date, new fields have been added over time) which can be used to generate the database and initial source code files:
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 original_ceremony: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[7.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 :expired_now, default: false, comment: "When true, the record will be permanently deleted (not just marked as deleted) at the next award ceremony."
t.boolean :expired_soon, default: false, comment: "When true a warning message is displayed to users when viewing this object (suggesting that they spend some points to revive it), doesn’t affect how the record is treated."
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: true, foreign_key: {to_table: :ledger_bases, name: "fk_rails_ledgercreator"}, comment: "Identifies the user who created this record, using their original ID."
t.float :rating_points_spent_creating, default: -1.0, comment: "The number of points spent on creating this Ledge object by the creator. Includes transaction fees. Since every object has a creator, the data about the creation spending is stored here in the object, rather than making a separate LinkBase record for every object. It also means we avoid the worries about approvals and deletion that come with a LinkBase record. If negative when creating a new object (but not in the database), use default spending amounts. If the creator of a LedgerObject is changed, the points used for creating it will be charged to the new creator and uncharged from the original creator, backdated to the original ceremony."
t.float :rating_points_boost_self, default: 0.0, comment: "The number of points used to boost the rating of this object, during creation. Should not be negative."
t.string :rating_direction_self, default: "M", comment: "Use U for up, D for down or M for meh. Controls how points spent during creation modify this object's rating."
t.boolean :bool1, default: false, comment: "Generic boolean, defined by subclasses."
t.datetime :date1, null: true, comment: "Generic date and time from year 0 to year 9999, 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.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 to force a recalculation of current points. If it is less than the most recent ceremony's number, the points just need an update recalculation."
t.integer :original_ceremony, default: -1, comment: "The number of the awards ceremony immediately prior to the creation of this object. 0 if before the first awards ceremony. Negative is an initialisation bug. Theoretically you could figure it out from the record creation date."
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.float :float1, default: 0.0, comment: "Generic floating point number, usage defined by subclasses."
t.bigint :number1, default: -1, 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 LedgerApprove."
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 LedgerApprove."
t.float :rating_points_spent, default: -1.0, comment: "The number of points spent on making this link by the creator. Includes transaction fees. Negative when creating a link means use default spending amount and default allocation for the various boosts, though won't be negative in the database."
t.float :rating_points_boost_parent, default: 0.0, comment: "The number of points used to boost the rating of the parent object. Should not be negative."
t.float :rating_points_boost_child, default: 0.0, comment: "The number of points used to boost the rating of the child object. Should not be negative."
t.string :rating_direction_parent, default: "M", comment: "Use U for up, D for down or M for meh. Controls how points spent modify the parent's rating."
t.string :rating_direction_child, default: "M", comment: "Use U for up, D for down or M for meh. Controls how points spent modify the child's rating."
t.integer :original_ceremony, default: -1, comment: "The week's award ceremony number when this record was created, 0 if before any ceremonies have been done. -1 if it hasn't been set yet (in which case it will soon be set and the point boosts added to the parent and child objects)."
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 or LedgerApprove) to LinkBase records (usually links being deleted or approved)." 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_meta_opinionator, default: "", comment: "Relationship expression specifying additional people who are allowed to rate opinions about messages in this group."
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_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 has_many :objects_created, class_name: :LedgerBase, foreign_key: :creator_id 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 :links_created, class_name: :LinkBase, foreign_key: :creator_id 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 has_many :link_opinion_targets, class_name: :LinkOpinion, foreign_key: :child_id has_many :link_opinion_authors, class_name: :LinkOpinion, foreign_key: :parent_id has_many :opinion_targets, through: :link_opinion_targets, source: :child has_many :opinion_authors, through: :link_opinion_authors, source: :parent 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, type, number1, string1, string2, text1, rating_points_spent_creating, rating_points_boost_self, current_meh_points, original_ceremony, current_ceremony, date1, created_at, updated_at)
VALUES
(0, 'LedgerUser', 0, 'Root LedgerBase Object', 'agmsmith@ncf.ca', 'The special root object/user 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.0, 0.0, 1000.0, 0, 0, '0001-01-01 00:00:00', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);"
)
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?
$Header: /var/www/ratingstone.agmsmith.ca/docs/RCS/Database\040Design.md,v 1.95 2023/09/13 00:47:03 agmsmith Exp $ $Log: Database\040Design.md,v $ Revision 1.95 2023/09/13 00:47:03 agmsmith Clarifying edit, view permissions for objects and links. Revision 1.94 2023/09/12 15:39:00 agmscoder Updated to use current database field declarations. Revision 1.93 2023/09/12 00:48:36 agmsmith Proofread up to Auxiliary Classes, fixing wording and clarifying things. Revision 1.92 2023/09/11 23:38:08 agmsmith Reviewing the docs, WIP, up to creator_id. Revision 1.91 2023/04/24 01:04:48 agmsmith WIP - Adding a section on object permissions, next up do LedgerBase, LedgerPost, more Links. Revision 1.90 2023/04/05 21:59:43 agmsmith Add recent new User record fields. Revision 1.89 2022/12/11 22:50:27 agmsmith Fix indentation of headings, remove birthday objects. Revision 1.88 2022/12/11 22:44:43 agmsmith Clarified LinkOpinion and LinkMetaOpinion, LinkMetaMod redundant. LedgerContent removed, was unneeded conplexification. LinkOwner not needed now that all LedgerBase objects have creation fees internally. Added Quotes functionality to LinkReply. Revision 1.87 2022/12/11 20:14:54 agmsmith Change meta_moderator to meta_opinionator for the Role. Revision 1.86 2022/09/13 19:51:39 agmsmith Decide that deleted flag in LedgerBase objects applies to all versions of that object at once. Revision 1.85 2022/09/04 21:32:26 agmsmith Don't charge creation to creator for LedgerUser objects, since they are their own creators and that would leave them with no points after creation. Revision 1.84 2022/08/18 18:05:38 agmsmith Oops, forgot one rating_points_spent_creating negative case. Revision 1.83 2022/08/18 17:59:12 agmsmith Set default points for parent/child/self boost to zero. Can have negative for amount spent to show that standard amounts will be spent to creat the object or link, but only at time of creation, not in the database. Revision 1.82 2022/04/11 23:17:09 agmsmith Rename field to expiry_ceremony. Revision 1.81 2022/04/11 21:46:40 agmsmith Add an end ceremony to LinkBonus. Avoids a problem with missing points. Revision 1.80 2022/03/01 21:47:47 agmsmith Add a virtual Link for creating objects, but represented by fields within the object rather than a separate Link record. Revision 1.79 2022/01/31 21:58:19 agmsmith More on awarding Meh points. User record described, vs LedgerUser. Revision 1.78 2022/01/29 21:13:04 agmsmith Switch to using meh points to receive the weekly allowance of reputation bonuses. Solves several problems to do it that way. Revision 1.77 2022/01/23 01:11:37 agmsmith Correct award_number to new original_ceremony naming. Revision 1.76 2021/09/12 21:49:00 agmsmith Added LinkBonusUnique class rather than adding a boolean to LinkBonus. Revision 1.75 2021/09/12 21:32:16 agmsmith More LinkBonus details. Revision 1.74 2021/09/09 19:59:43 agmsmith LinkReply and LinkGroupContent can be deleted by users with just approval permission. Revision 1.73 2021/09/09 01:56:42 agmsmith Deleting links needs to be more strict, people who can do approval shouldn't necessarily be able to delete. Revision 1.72 2021/09/06 19:38:51 agmsmith Renamed rating_ceremony as original_ceremony to be consistent with the way the LedgerBase is named. Revision 1.71 2021/09/05 19:37:35 agmsmith Reorder so parent field documented before child field in LinkBase. Revision 1.70 2021/09/02 13:54:18 agmsmith First draft of LinkBonus. Revision 1.69 2021/08/25 16:50:38 agmsmith Added a comment to the award ceremony record. Revision 1.68 2021/08/24 22:01:21 agmsmith Remove checkpoint from example of database migration. Revision 1.67 2021/08/24 21:52:02 agmsmith Remove the checkpoint idea, it's actually useless. Revision 1.66 2021/08/24 16:53:00 agmsmith Work in progress - thinking about checkpointing points. Revision 1.65 2021/08/22 21:41:41 agmsmith Add checkpoint for current points at start of week. Revision 1.64 2021/07/26 14:41:18 agmsmith Update database migration example for new expiry fields. Revision 1.63 2021/07/26 14:27:35 agmsmith Add expired_soon, and delete links when object gets permanently deleted. Revision 1.62 2021/07/24 22:34:58 agmsmith Renamed LedgerChangeMarkings to singular, wording updates. Revision 1.61 2021/07/22 00:01:15 agmsmith Add expired field to the database migration example. Revision 1.60 2021/07/21 23:33:36 agmsmith Change database migration comments back to LedgerDelete or LedgerApprove. Revision 1.59 2021/07/21 21:57:31 agmsmith Switch to subclasses for LedgerDelete and LedgerApprove. Revision 1.58 2021/07/15 01:32:12 agmsmith After another proofreading run. Revision 1.57 2021/07/13 23:59:23 agmsmith Added opinionator role, message moderation and meta opinions updated. LedgerDelete and LedgerApprove et al replaced by LedgerChangeMarkings. Ceremony rework in progress. 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