Schema Proposal Round 2

[cbrumbaugh]

@cellio

Some of those fields, like the ones about SE accounts and importing data, seem like they should be at the community level, not the instance level. I might have imported my data from Writing but not (yet) from Worldbuilding.

These fields are duplicated at both the Users and UsersCommunities table in order to take care of both someone wanting to import all, and someone wanting to import just a specific community. That’s also why the Bio field etc. is on both places.

@cellio

What is IsSameAsInstance in this instance-level table?

This was proposed by manassehkatz

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”.

here

@cellio

What are HelpText and FAQ – literal text or URLs or what? Ultimately a community will have many help topics, many of which will be common across the instance.

I would say that those are the starting help text fields, as you mentioned we will probably have more than just the one, but that structure hasn’t been fleshed out yet.

@cellio

IsBeta implies that there are two kinds of sites, beta and not. We haven’t talked about beta-ness at all yet, and I think one thing we’ve learned from SE is that there are more than two natural stages to a community. If we have the notion of site types/stages let’s make that an enum so we can adjust it when we need to.

This makes a lot of sense will change it next iteration.

@cellio

The ID column here is the actual trust level, right? (0 through 6 as currently proposed.)

I think I goofed up here, there should be a TrustLevel table where the id column is the actual level and then a TrustLevelCommunity table for each community to customize the text. This would also be where the customized how many items it would take to progress to a level.

@cellio

Count me with those wondering whether Can[many things] is the best approach. I was expecting something like:

With more time to think about this, I am leaning towards reference tables too.

Proposal - Use Enum Tables for User Permissions

IsSuspended does make sense. And yes reference table would be more correct

@cellio

I don’t understand this one. What’s this for?

VoteTypes is a reference table for votes, upvote and downvote right now.

@cellio

General question: how does edit history get stored? Posts, comments, and user profiles should keep history. Is each revision of something a new row in the corresponding table, and the server code is responsible for finding and displaying the latest one and making the rest available via history links where applicable?

See this,

History/Audit DB

This has been kicked around before in a couple of places, but I would like to formally propose it here. The basic idea is that we create a separate DB for history and audit purposes. The name of the db with be the same as the normal one, just with _chg appended to the name. The history DB will have all the same tables as the normal one, with _chg appended to the end. Each table in the history DB will have 4 extra columns. Date of record. What activity was being recorded INSERT,UPDATE_BEFORE,…