Proposed DB Schema

[AMcBain]

I've proposed a new schema on the wiki at https://github.com/trevormarvin/conflusion-mt/wiki/Schema-Proposal

The only question I have is less structural: Do we want to account for "Confusion"-type memberships here? I would think no, since this system doesn't (and isn't intended to?) handle payments. Wheelhouse will have another deal to map if we do, and in any case all of them get the same rights as an existing membership level. Reporting it here as that level would make it easier for front desk volunteers (and members themselves) to see what they are entitled to.

[trevormarvin]

Addressing your 2nd item at this moment: The project name may betray this, but at the moment I am not intending this to directly be a cross-organization thing. I put the extra "L" in the project name partly to distinguish it from our other membership deal. I do plan, however, to solicit Fuse to join this endeavor, as I believe it can build something better than what they have, and we have very similar needs.

As for our particular needs regarding Wheelhouse access, I am accounting for that in the list of "certifications" and other "accesses" a particular member has. In our setup, all members would be under the Confluent umbrella, but specific members, like those of Wheelhouse only, would not receive any "certifications" of Confluent resources, only Wheelhouse resources.

[AMcBain]

Yeah that's kind of what I was getting at. If you're a member of both you'd have one of our membership levels, just perhaps at a discount in the case of Fuse, so the screen/db for this project would just list that level.

In the case of Wheelhouse if they just had Wheelhouse membership and not one with Confluent then it would list None (null) for the membership but a Certification to use the Wheelhouse bench and tools.

[AMcBain]

one of our standard membership levels*

[trevormarvin]

For the first part of your question, I looked at your Wiki entry, I believe much of what you request I already have procedures for in my schema, though I may not have conveyed them fully. As for how to approach answering you there: should I be editing your wiki entry and providing some sort of following response? Maybe I should be putting my schema in wiki form? Or maybe I should just focus on expanding my comments in the file for now (until it settles on something we're moving forward on)?

[AMcBain]

Maybe a bit of B and C? I think I missed your schema the first time around (I see it now in code) and it would probably be good to have it "on paper" in some format that is app-language independent. Would make it easier to see the relationships perhaps and contribute suggestions.

You can edit/blow away my wiki page to serialize yours if you want to keep it all in the same place. (The wiki is revisioned afaik.)

[AMcBain]

I've now taken a look on a proper display (desktop computer) and there's things in mine that appear to be missing from yours (assuming it's all in models.py).

Firstly there's no membership stuff, but I presume that will come. Secondly it tracks events which we could do, but that seems a bit out of scope. I thought we were creating a way to track memberships, permissions / access rights (certifications), and help out the front desk when members walk in.

We don't currently have any events that have advance ticket sales. Even The Glow Show is done at the door for everyone. If, as said above, we're just designing this for us first and not for joint adoption, I think event stuff is getting ahead of ourselves.

I think there is something to the Location piece, aside from events, but in my proposal where that would go is covered by Certifications, unless you think not. Wheelhouse's location is inside of our location, but presumably they train everyone who is a member in using the tools they provide. So for them being a member might just result in a quick check of a box for the Bike Tools & Workbench Certification.

However, I do think the parent-child thing you have going for Certifications is interesting. In mine I went with a separate Certification Group merely based on Stew's line of wanting to certify only particular tools in the shop he doesn't want to imply more by certifying for "The Woodshop." This line of thinking does make for some implicits rather than your explicits: access to the woodshop is granted upon being certified for a tool inside of it. In your case we could just name the parent Certification "Access to the Woodshop" (or similar). In the end I wouldn't fight to keep my Certification Groups over your self-referencing approach.

[trevormarvin]

A bunch of the stuff in the schema, like event calendar scheduling and ticketing, is merely a road map to ideas of things that could be added to this. Like I discussed directly with Lance, my approach in a new project is to brain dump everything the project could possibly entail at the start, but then pare it down to what we have the resources and need to actually bite off. All the other stuff is there to serve as a reminder as to what may be added, so certain architectural decisions are not made that would preclude adding them later. Thus, I have no expectations that location scheduling gets built soon... but I was having a discussion with a Fuse member today and that is something they would utilize. (Though they're using another system for the forseeable future.)

The 'membership' stuff is actually mostly handled in the Django 'users' module add-in. I'll get that copied into the schema as comments for a single reference to the complete schema. We can then augment Django's user table as we see fit.

Regarding the parent-child certifications relationships, and specifically the woodshop example; I wasn't thinking that one necessarily gets access to the parent by having access to the child. It was more of a way to approach grouping them when it came time to displaying or finding a particular cert.

My intention in keeping 'locations' separate from 'certifications' is that the 'locations' eventually become the resources that a scheduling and reservation system looks at. Access to said location was something I was shoe-horning into 'certifications', i.e. "certified to access a particular location by yourself, the access control card checking that you are certified to operate the location's door knob". ;-)

[trevormarvin]

My "certifications" and "certified" tables are analogous to Django's built in "group" and "permission" models. I'll entertain the idea of switching to those. My inclination is to keep down this separate path and it'll form something with structures tuned to our needs, and I don't foresee any major functions/procedures that Django's built-ins have that do exactly what we need or would be too hard to re-implement.

[AMcBain]

I kind of feel that we've got two separate problems here: tracking memberships/certifications and location reservations. To be honest, room locations can be solved by having a Google Calendar with multiperson access rights and good manners. Don't schedule a meeting for a location if it's already got one. Then just have a Pi+screen or something outside each room that shows the schedule/busy state. SparkFun solved it that way, iirc. (They have an article on it, I think, and some OSS for it somewhere.)

As to the certification tracking, each item could just check membership level or certifications that pertain to them rather than involving locations. We only have one location (more if you count the shops and labs as their own), and no 24/7 access (it's not currently on the roadmap, either, as far as I'm aware).

If that is later deemed inadequate, then if all the hypothetical items they're "swiping in" with hit a service in this app (the backend) we just have to update the DB and the services. A change to add locations doesn't seem like it's a destructive addition if we were to wait on it.

[trevormarvin]

Repeating: I have no delusions that we will be building a room/location reservation system anytime soon; unless programmers from our friends down the street wish to jump in and do that. The location table is not necessary to the access control; access can become a "certification" on operating a particular door.

Certain certification could be tied to membership levels; I haven't put that concept in the schema. I would lean away from a membership level approach where every level included all the levels below it; there's too many places that will break. Adding an access to the perks of a particular membership package as a certification, though, I think can work for our needs.

The 24/7 access levels are on this roadmap, they merely become certain certifications. e.g. The front door, depending on the particular time of day, would check a different certification to be operated.

I count our current location as having multiple locations that have differing access permissions. The need to have some parent/child relationships between these locations only becomes critical with a scheduling and reservation system. But please, dissuade yourself of the notion that I'm suggesting we build that any time soon.

[AMcBain]

Repeating: I have no delusions that we will be building a room/location reservation system anytime soon; unless programmers from our friends down the street wish to jump in and do that. The location table is not necessary to the access control; access can become a "certification" on operating a particular door.

OK.

Certain certification could be tied to membership levels; I haven't put that concept in the schema. I would lean away from a membership level approach where every level included all the levels below it; there's too many places that will break. Adding an access to the perks of a particular membership package as a certification, though, I think can work for our needs.

That wasn't really what I was trying to suggest. I know it's not necessarily good to have membership level "access rights" cascade. Yes, the door would probably be better as a certification but... this breaks down too because

The 24/7 access levels are on this roadmap, they merely become certain certifications. e.g. The front door, depending on the particular time of day, would check a different certification to be operated.

this is not on the roadmap if you mean membership levels offering 24/7 access. We discussed it at a board meeting two meetings ago and there were more issues than just the front door. If you mean front desk volunteers, who we've fully vetted, that enjoy 24/7 access as a perk, then it seems reasonable to accommodate them under a new system since they would need it anyway to do their job.

I count our current location as having multiple locations that have differing access permissions. The need to have some parent/child relationships between these locations only becomes critical with a scheduling and reservation system. But please, dissuade yourself of the notion that I'm suggesting we build that any time soon.

Yes, you have made that clear. My point is just the opposite view of one of your above comments: Why build it in code first if we're not going to do it? We have a wiki to store our brain dumps and roadmaps. I'm worried this project will have scope issues, and I think given what we're doing and who we are, it's not hard to build something intelligently without locking ourselves out of future possibles (that may or may not ever happen). It's not like my proposal on the wiki would need rearchitecting to add location info, for example, and I wasn't even thinking about it explicitly.

In the end though, and to stop the dancing around, the location stuff doesn't really seem to hurt anything (assuming it's just that, and the ticketing/reservation stuff gets dropped, etc.), but it seems to be solving a physical access control problem that we don't really have† or currently have things to support.

† Things work as they currently are, aside from requiring slices of dead trees to store everything. Could it be "better" or more streamlined, certainly, and that's what I think we wish/want to solve here. However it's not an immediate need.

[AMcBain]

Sorry, I edited that a few times. My grammar sucked and I forgot it put items right after quotes in with the quote if you didn't have an extra line break.

[trevormarvin]

I guess by having it in the schema it's essentially in the code. I wasn't seeing it that way, but I'm not calling that an unreasonable view. If it would help the code not appear so convoluted and messy, I'll separate out the roadmap stuff I'm suggesting to another branch or maybe other files so that what we're currently working with is more concise and topical to the projects at hand.

Whether or not a membership level granted a particular 'certification', "24/7 access" in our current example, was not something that I was worrying exactly what the boards' current decision is on that... because I'd treat it as a discrete thing to be given per member. Thus, the policy for actually granting it would probably be one item of a checklist when granting that membership level.

[trevormarvin]

Should we purpose the built-in Django "Groups" functionality to reflect membership level? Permissions granted purely on membership level could reference the 'groups' table, permissions granted on discrete certifications with the basic auditing of granter would reference the custom 'certifications' table.