[wplug-internet] Membership Database design

[Pat Barron]

I need to think about this for a bit, hopefully tonight after work. I 
may have a bit more DB experience - but not by much.  ;-)

The one comment I have off the top of my head is that we should probably 
use something else besides the e-mail address as a primary key, since 
e-mail addresses may change.  Maybe let the DB auto-generate a sequence 
number when a contact is added (and use that sequence number as the 
primary key).  Since we do want to include e-mail addresses (maybe just 
not as the primary key), I would also suggest adding a constraint to 
prohibit more than one row from having the same e-mail address, which I 
think will spare us the possibility of some nasty bugs later on.  I do 
agree with keeping separate tables for people, and membership details, 
in case any of the contact records don't actually have a membership 
associated with them.

Hopefully more later...

--Pat.

On 12/19/2014 12:16 PM, Joe Prostko wrote:
> On Thu, Dec 18, 2014 at 11:59 PM, John Lewis <oflameo2 at gmail.com 
> <mailto:oflameo2 at gmail.com>> wrote:
> >
> > I have a schema I designed in sqlite3 today. If you like it I can port
> > it over to MariaDB.
>
> Seeing as we're likely not going to be doing crazy stuff like multiple 
> table joins, we could probably get away with just having one table 
> with all of the various columns. Also, I'm a bit rusty on SQLite, but 
> it looks like you're trying to use the email address as something like 
> a primary key.  I don't think that is the best idea ever, since email 
> addresses change.
>
> In MariaDB, we'd just use an autoindexing primary key, and go from there.
>
> In any case, being a DBA is not at all my specialty (so I'm likely 
> confused about the details), but I'd rather keep things as simple as 
> possible, even if it means more columns in one table.  Then again, 
> it's better to do things right the first time, so it might make sense 
> to keep separate tables like you suggested, depending on the needs of 
> different people.
>
> I'm sure somebody with more knowledge will chime in to give comments.
>
> - 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/20141219/acffef84/attachment.html>