[wplug-internet] Membership Database design

[John Lewis]

Hey, this project is not finished!

On 12/19/2014 08:10 PM, John Lewis wrote:
> Its later now.
>
> I went with a database design that used natural keys instead of
> surrogate keys because surrogate keys are still confusing to me.
>
> Surrogate keys are faster to compute, but the database is microscopic
> currently and it isn't expected to grow to even 100MB.
>
> Could one of you show me the same sqlite3 schema except using surrogate
> keys instead of natural keys?
>
> On 12/19/2014 12:34 PM, Pat Barron wrote:
>> 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
>>
>>
>>
>> _______________________________________________
>> wplug-internet mailing list
>> wplug-internet at wplug.org
>> http://www.wplug.org/mailman/listinfo/wplug-internet
>>