[wplug-internet] Membership Database design

[John Lewis]

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
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.wplug.org/pipermail/wplug-internet/attachments/20150103/f96194f1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: memberSchema3.sql
Type: text/x-sql
Size: 666 bytes
Desc: not available
URL: <http://www.wplug.org/pipermail/wplug-internet/attachments/20150103/f96194f1/attachment.bin>