[wplug-internet] Membership Database design

[John Lewis]

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/20141231/a168b448/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: memberSchema2.sql
Type: text/x-sql
Size: 567 bytes
Desc: not available
URL: <http://www.wplug.org/pipermail/wplug-internet/attachments/20141231/a168b448/attachment.bin>