[wplug-internet] Membership Database design

[John Lewis]

Final warning!

If I don't see a line of PHP code posted to the wplug-internet mailing
list by Wednesday, I will just start dumping code.

On 01/03/2015 05:09 PM, John Lewis wrote:
> I rewrote the schema and and installed it on the MariaDB server. I
> attached the schema to this email. I didn't make a user or grant
> privileges to a non-root user yet because I don't know what user is
> going to need to read it yet because I don't anything about the PHP
> application that is going to read or write to it.
>
> I realized that sense we are using surrogate keys it would be efficient
> to find the email address via the join on renewal(contact_id) because it
> would be related to exactly to one email address. The email address can
> change without having a cascade update.
>
> In either case, it is time for the PHP part now.
>
> On 12/31/2014 07:47 PM, John Lewis wrote:
>> There is not really a good reason to use SQLite3 in production when we
>> already have a perfectly good database server running.
>>
>> I am developing the initial schema in SQLite3 because there is a lower
>> barrier of entry for setting up an SQLite3 datatabase than a MariaDB
>> database, so we can develop and test a workable schema faster without
>> needlessly putting the production database server at risk. I never
>> suggested using it instead of MariaDB and was always was planning to
>> rewrite the schema once we agreed to a database design.
>>
>> I have yet another new schema attached to this email.
>>
>> If you have an integer as a primary key and don't populate it, SQLite3
>> assumes you want an auto-incrementing primary key.
>> https://www.sqlite.org/autoinc.html
>>
>>
>>
>> On 12/31/2014 12:23 PM, Joe Prostko wrote:
>>> On Wed, Dec 31, 2014 at 6:34 AM, John Lewis <oflameo2 at gmail.com
>>> <mailto:oflameo2 at gmail.com>> wrote:
>>>
>>>> That said I don't think it is a good idea to reduce this to just one
>>>> table because Just as Pat said there is no guarentee that every contact
>>>> has a membership associated with them.
>>> Using multiple tables does likely make more sense, simply because we
>>> may make other categories of users and be tracking things outside of
>>> membership.  It's probably better to plan ahead for such a thing
>>> instead of having to worry about overhauling things at a later day
>>> due to a choice that was too constraining (and would result in an
>>> unmanageable amount of columns).  That said, a single table could
>>> still easily work for the current use case with proper WHERE clause
>>> logic (checking for NULL/IS NOT NULL values in certain column(s), or
>>> checking a column that flags if somebody is a member or not, for
>>> instance).  I suppose it could get trickier if somebody was a former
>>> member though, as you would then have to do date comparisons (MySQL
>>> makes that easy though), or else rely on the binary flagging method.
>>>
>>> In any case, two tables definitely works, so let's do that.  :)
>>>
>>>> I see if I can make a database in sqlite3 that auto creates an auto
>>>> incrementing primary key. If I can and it make sense, I will post it
>>>> here. Then I will rewrite it for mariadb, post that here and then
>>> commit
>>>> it to wplug.org <http://wplug.org>'s mariadb so we can move on to
>>> the PHP part so we can
>>>> finish a project. I can go ahead and prepopulate the database with all
>>>> of the data from the spreadsheet if requested.
>>> Honestly, we could simply use SQLite for this, most likely.  It seems
>>> we have the necessary PHP modules already enabled from my quick
>>> check.  I usually use MySQL (or variants like MariaDB or Percona),
>>> simply because phpMyAdmin is such a nice tool for those times when
>>> you don't want to mess around with the DB directly.  I don't think we
>>> actually have it installed though, so using SQLite would be just as
>>> easy, currently.  Database backups would be easy too, for obvious
>>> reasons.  I guess it depends if we'd like to integrate this fully
>>> with the wiki or not, or if we want to it to act more standalone.
>>>
>>> - joe
>>>
>>>
>>> _______________________________________________
>>> wplug-internet mailing list
>>> wplug-internet at wplug.org
>>> http://www.wplug.org/mailman/listinfo/wplug-internet
>