[wplug-internet] Membership Database design

[Pat Barron]

Also, re-reading this, I think I pretty much said the same thing Joe 
did....  ;-)

This is why I need to think about this later - it's way too early in the 
day for me to be making any sense...  ;-)

--Pat.

On 12/19/2014 12:24 PM, Pat Barron wrote:
> 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
>
>
>
> _______________________________________________
> 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/e6e38a79/attachment-0001.html>