Database schema (Round 8) - wip

Software Development
21

OK, but isn’t that only at SaveChanges?

eg doesn’t this work?

using (CodidactContext context = new CodidactContext())
{
  Member member = new Member {
    DisplayName = "new member",
    Bio = "new member bio",
    ...
  };

  member.CreatedByMember = member;
  member.LastModifiedByMember = member;

  context.SaveChanges();
}
22

Is there any reason, but consistency with other tables, to have a created_by_member column on a member record? Will this be used anywhere?

23

All tables have those 4 columns as part of the spec (DB Naming convention - Round 2 - #8 by manassehkatz). Their purpose is audit related. My personal preference would be to make them not nullable for the member table rather than delete them entirely.

@misha130 One more suggestion: a dummy member could be created and the member foreign keys can initially be set to the dummy user, and then changed to the new user. If both SaveChanges() calls happen inside a transaction it should guarantee the correct member_id is set, but you’d need that dummy user created beforehand. I don’t think that would trigger the circular reference error.

If you don’t like that idea, I think you’ve identified a valid justification for allowing the created_by, and last_modified_by keys to be nullable, so I’d suggest to assume their NOT NULL status has been removed on the member table. I’ll set that in pgModeler and announce it in the next round and see if there’s any disagreement. The keys could still be set in a second SaveChanges call within a transaction so any error on setting the keys would roll back the entire creation attempt.

24

Ok I honestly have a circularity problem with the TrustLevel right now.

Member:
Needs on created by/modified by - no problem, that is set to itself.
Also needs to have a trust level because every member has a trust level when its created.

Trust level:
Needs to created by/modified by members.

So thats the circularity I am now facing. I posted the code for all of this on discord if you want to take a look.

So maybe make TrustLevel on member nullabe? Or maybe some of these tables shouldn’t have these columns?

25

Yup, I was actually going to say something about that, but figured the TL foreign key would just cause the same result as the member foreign key. So either Member ↔ TrustLevel foreign key cannot be NOT NULL, or maybe TrustLevel could be set up as an enum/type which would remove the created_by, last_updated_by references. Fortunately this is the only (non-member) foreign key in the member table.

I’m still a bit curious if adding the member as the created_by_member works, but I wouldn’t waste time on it.

It seems a strategy will need to be created to perform a full install. A system member will need to create default data in TrustLevel and possibly any other lookup type tables as part of the install - also enums. (I’m getting more and more convinced we need at least one non-human member)

So assume trust_level_id is a nullable FK for the member table and I’ll make it like that for the next schema proposal and ask if that alternative is preferred over making trust_level an enum.

26

Well sadly the following doesn’t work too:

    communityMember = new Member
     {
      DisplayName = "Codidact Community",
      Bio = "The codidact community manifested",
     };
     communityMember.CreatedByMember = communityMember;
     communityMember.LastModifiedByMember = communityMember;

     context.Members.Add(communityMember);
     context.SaveChanges();

It wouldn’t work if you wrote pure SQL too and EF isn’t some voodoo magic.
So I am going to let the createdMemberById/lastmodifiedbyMemberId be null

1 Like
27

Agreed; it’s smoke and mirrors. I guessed/hoped there was some temp creation going on in the background to satisfy constraints

Yup, works for me. As I said, I’ll set that in pgModeler, announce it in the next round and see if there’s any disagreement. The keys could still be set in a second SaveChanges call within a transaction so any error on setting the keys would roll back the entire creation attempt.