[wplug-internet] Membership Database design

[John Lewis]

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
>