[wplug-internet] Membership Database design

[John Lewis]

I removed the comments table because I didn't think there is enough data
to define a good primary key for the table.

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.

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'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.

On 12/27/2014 07:45 AM, John Lewis wrote:
> 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
>>>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: memberSchema1.sql
Type: text/x-sql
Size: 483 bytes
Desc: not available
URL: <http://www.wplug.org/pipermail/wplug-internet/attachments/20141231/8df442ca/attachment.bin>