Proposal - Schema

Software Development
5

In addition to my comments below, almost every table will need History (aka Audit Trail aka Logging). In some cases this will be obvious (Edit history of Q&A which can be viewed by anyone) and in some cases behind the scenes/moderator only (to research various types of bad behavior). In most cases this will be “another table same as the original with a prefix in the table name, FK to the original table, a copy of all fields from the original table plus a few extra fields to indicate who/when the change was made”.

I notice here and elsewhere many text fields that are 255 or length-not-specified. I think that in many cases a maximum length, often less than 255, is beneficial as it can help with display/UI related issues. For example, a Username at 255 characters would be a bit unwieldy given all the places it needs to go, but the limit can’t be too short as it would prevent things like adding “Reinstate Monica”, so SE’s arbitrary (and any value will be arbitrary) 40 characters for DisplayName is too limiting. I will suggest some limits but they are arbitrary.

Add:

  • DisplayName (Text 100)
  • Bio (Text)
  • Personal Website (Text 255)
  • Github (Text 100)
  • Twitter (Text 100) Only if it is also in UserSites. But I would be thrilled if Twitter were not an inherent part of Codidact as I see it repeatedly used in awful ways (on all sides of the political and other spectrums). People can always stick it in their Bio.
  • Location (Text 100) optional
  • IsFromStackExchange (Boolean default FALSE)
  • StackExchangeId (Bigint)

Most of those fields are in order to have a “master” user definition used when a user connects with a new community or in an existing community sets “IsSameAsInstance”.

As far as IsFromStackExchange and StackExchangeId, not sure how the whole import process will be handled, but I believe the plan is to have some form of “Import a lot of stuff and then let users claim their stuff” - and this is for that purpose, though the details may dictate a different way of storing this.

Suggest considering calling this Communities. It fits with the theme of the whole system (a community of people rather than just a site visited by a bunch of anonymous people) and avoids ambiguity between the entire system (Instance) and individual Communities. On the other hand Community vs Communities can confuse things (as opposed to Site vs Sites and Instance vs Instances where you just add an ‘s’) and Sites is a lot shorter than Communities.

Changes:

  • Name (Text 40) UNIQUE - this needs to be fairly short for a lot of display, link, etc. reasons.
  • Tagline (255) UNIQUE - needs a limit, though arbitrary
  • URL (100) UNIQUE - needs a limit, though arbitrary - but a lot of reasons it shouldn’t be very long

Add:

  • HelpText (Text)
  • FAQ (Text) - or might be a bigger structure, TBD
  • IsActive (Boolean default TRUE) - could be FALSE while a site is under development or if ever disbanded
  • IsBeta (Boolean default FALSE) - aka Area 51

plus a ton of other fields that will be used to determine lots of settings relating to voting, “points”, trust levels, etc.

Change:

  • Name (Text 40) UNIQUE

We might want to make the TrustLevel names community-specific, in which case this would have Community FK as well (with None = primary Instance, and if there is no match for a Community, defaults to using the Instance Name for each TrustLevel).

Changes:

  • DisplayName (Text 100)
  • PersonalWebsite (Text 255)
  • Github (Text 100)
  • Twitter (Text 100) Consider removing

Add:

  • IsSameAsInstance (Boolean default TRUE)
  • StackExchangeValidated (Date)
  • StackExchangeLastImported (Date)

Add (unless it is all in another table somewhere):

  • StartDate for each of the included EndDate fields.
  • Reason - not sure what form this will take - might be FK to a “Reasons” table or might be text, moderator, etc. But need to store somewhere why/when the action was taken.

Add:

  • UsersId (bigserial) - probably the most important thing of all, except for Title & Body!
  • IsQuestion (Boolean default TRUE)
  • IsAnswer (Boolean default FALSE)
  • IsBlog (Boolean default FALSE)
  • IsCanonical (Boolean default FALSE)

or alternatively have a PostType.

  • IsClosed (Boolean default FALSE) - need to also figure out how to link to Closed info (who voted, reasons, etc.)
  • NetVotes (Bigint)

Change:

  • Title (Text 255 default NULL) - Note that NULL is only OK for Answers. Q, Blog, Canonical will always need a title.
  • Score (some sort of decimal) - this will be the calculated/weighted score used internally for ranking as discussed elsewhere (with the simplest variant being the same as NetVotes)
  • As noted by others, all vote counts should be Bigint, not Smallint.
  • Downvote should be Downvotes (plural for consistency)

Add:

  • UsersId (bigserial) - probably the most important thing of all, except for Body!
  • NetVotes (Bigint)

Change:

  • Downvote should be Downvotes (plural for consistency)
  • Score (some sort of decimal) - this will be the calculated/weighted score used internally for ranking as discussed elsewhere (with the simplest variant being the same as NetVotes) Not sure if needed on Comments - if not needed then remove it and just use NetVotes to match the meaning on Posts.

Add:

  • TagWiki (Text default NULL) - The Description is relatively short (though 255 may be too short so not setting a limit) and should be plain text. TagWiki can be full Markdown - could be as much as a Canonical post about the Tag
  • IsActive (Boolean default TRUE)
  • TagsSynonymId (bigserial FK to Tags default NULL) - Use this to indicate a synonym - simply deleting synonyms doesn’t work because users will search and either not find and be frustrated or not find and decide to add in an endless cycle. This will point them to the “approved” tag.

Change:

  • Name (Text 100 NOT NULL)
  • Usages (Bigint)
2 Likes