Database schema

[CarwynNelson]

Requirements

We should think about the database schema before we write it in Django. This should result in a better design, act as documentation, and make it easier for others to contribute to building the membership management system.

It would be ideal if this schema lived in the git repository in a format that could be easily edited by others. This would give us the opportunity to update it over time, even if we choose not to take that opportunity.

[jamesgeddes]

Started work on the ERD https://drive.google.com/file/d/1FRs9OadlomCcLuXkOrLBaGM34b7f8bvU/view?usp=sharing Once I have done this, I will SQLify it

[jamesgeddes]

@CarwynNelson any thoughts?

[jamesgeddes]

Added to repo https://github.com/GeekZoneHQ/web/blob/master/web/Web%20App%20ERD.xml

[CarwynNelson]

Great start! I've just given it a quick look over and here are my initial thoughts:

general comments:

• Could we remove the table name prefix from most column fields. Stuff like membership_id makes a lot of sense, but member_fullname feels a bit ott imho

member table:

• Do we need to store the members title?
• If we do could we store this as an ENUM instead. I don't think this would need an extra table just yet?

membership table:

• what is the end_timestamp intended for? Could that be calculated based on the start_timestamp + membership_term? Or does this account for early cancellations etc?
• Would it be worth having a foreign key to a payment in this? This would make it easier to:
  • Ensure a member has paid for their membership
  • Track the payment for a membership down more easily if we need to deal with payment related enquiries
• Or maybe we could store a direct debit reference? Maybe the payment table could also store direct debits? Something to think about for the future I reckon :)

membership_type table:

• What is a term_duration?

payment:

• What does gbx stand for?
• I assume this table is intended for one off payments?
• This will probably become clearer when we integrate with a payments provider, but I reckon we will want to add a payment_reference to this table
• Not one for now, but I can imagine we might want to store direct debit payments in the database. Either in this table every time gocardless tells us that a direct debit payment has been received, or in a separate table?

Also, could we make any changes in a pull request? Makes it easier to review them, and avoids lots of unnecessary commits getting into master and makes the commit history nice and clean :)

[jamesgeddes]

• Could we remove the table name prefix

Absolutely

• Do we need to store the members title?

We need to have the ability to add new titles as they arise in the future. Happy to do this however you think would be best; as you say, more tables = more delay. Perhaps I'm overkilling again.

• what is the end_timestamp intended for?

I was a bit torn on this one. Regulation 26 of The Charitable Incorporated Organisations (General) Regulations 2012 states that we must provide a register of members to the Charity Commission (CC) which records,

(a) the name of the member; (b) a service address of the member; (c) the date on which the person was registered as a member; (d) a statement of the class to which the member belongs; and (e) the date on which the person ceased to be a member.

We, therefore, need a way to provide the expected renewal date (for internal use) and the actual leave date (for the CC). Perhaps the former can be calculated and the latter could be stored? Again, I'm open to suggestions.

• Would it be worth having a foreign key to a payment in this?

What if the payment is not for membership? We will also integrate the shop at some point in the future. In my mind, we would tell the member that £x is due. When £x+y arrives against their account, x would be counted as covering the due and y would be counted as a donation. I guess an "order" table would be a good idea for full audit purposes.

• Track the payment for a membership down

If we need to do manual processes then something has gone wrong lol

• maybe we could store a direct debit reference

I'd think we would tell the member to use their member ID as the reference on all payments. If it is a direct debit, I believe that gocardless is very flexible.

• What is a term_duration?

Again, I'm probably thinking about this all wrong. We need the ability to set a membership type of x (type_term) hours/days/months/years (term_duration). Say we need a one-month membership, we cannot define a month as exactly 730 hours, as no months have the average number of hours. We can, however, use relativedelta to calculate,

from dateutil.relativedelta import relativedelta
current_date = datetime.datetime.now()
end_date = current_date + relativedelta(term_duration=type_term)

I'm sure there's a better way around it than this though! If only we could use the metric clock and calendar...

• What does gbx stand for?

GBX is the currency symbol for Penny Sterling. Means we store Sterling as an INT. All Geek.Zone prices will be based on Sterling, because reasons.

• I assume this table is intended for one-off payments?

This table could track all payments a member makes. I guess we would need an order table too so that we can know what the payment was for (kina what you suggested above).

• This will probably become clearer when we integrate with a payments provider, but I reckon we will want to add a payment_reference to this table

Agreed, I will add this to the orders table.

• gocardless tells us that a direct debit payment has been received

We should consult the gocardless docs. PCI compliance should be avoided at all costs!

could we make any changes in a pull request

Sorry :/ I just saw the big "Export to GitHub" button and got all excited...

Actions

I will;

• [x] Reduce field name lengths
• [x] Add order and related tables to track sales, including but not limited to membership, donations and merch.

Have I missed anything?

[jamesgeddes]

Funfact, draw.io offline is a thing.

[jamesgeddes]

Added erd branch. Reduced field name lengths and started the order table tree, but its 0230 and my brain is not working well enough to finish the logic.

[CarwynNelson]

Resolved in #5