Database file structure: Comments needed.

[GoogleCodeExporter]

Do we actually need all those city/region fields, or will it just cluter
our first task into creating a secure login methodology? 

Preferably you would store such info in a side table (name, metafield,
metavalue) or something. Similar to a preferences table you would use for
"news" statement.

What fields should exist in the registration table.

Original issue reported on code.google.com by rdivilb...@gmail.com on 6 Feb 2010 at 11:18

[GoogleCodeExporter]

I'm sorry, this is the users table.

Currently:
FIELD       TYPE        SIZE    NOTE
id      integer       4 autoincrement
dateRegistered  date/time     8
userid      varchar      50
password    varchar     255
email       varchar     100
ip      varchar      32
region      varchar      50
city        varchar      50
country     varchar      50
useragent   varchar     255
website     varchar     100
news        char          3
locked      char          1
dateLocked  date/time     8
token       varchar      64
deleted     char          1
dateDeleted date/time     8

Original comment by rdivilb...@gmail.com on 6 Feb 2010 at 11:31

[GoogleCodeExporter]

We will want to determine the level of security for sensitive fields.

password - hashed or encrypted : personal prefer hashing passwords and offering 
reset
capabilities, instead of encrypting to allow for emergency retrieval. 
definitely not
clear text.

email - in the past I have left this clear text, but given that could be used 
for no
good these days if harvested, do we want to consider some encryption here? 
don't feel
as strongly about this as passwords.

ip - may want this encrypted or stored in some other obfuscated manner.

on the region, city and country - since those are to be derived from the ip 
address
logged instead of an entry, I would suggest some normalization where we have a 
table
that holds ip ranges that equate to a given region, city and country 
combination. 
this way you will have one record for storage that could end up being 
associated to
many users whose ip address fall within a given range.

could probably find some other good candidate fields for something like that to 
make
this optimal for varying levels of users.

Original comment by b1ackKni...@gmail.com on 6 Feb 2010 at 11:44

[GoogleCodeExporter]

Thw wiki specifies the password is a hash.

A change password page, a request password recovery page, and a password reset 
page
are also outlined in the wiki because we don't know the password as it is 
hashed.

I open to the group consensus on e-mail encryption.  That will require changes 
to the
code base.

I don't feel a need to obfuscate the regestration ip.

The city, region and country are retrieved from an online database which is 
regularly
updated. I would not want the burden of having to keep another copy updated 
when that
one is freely available.  One could argue to eliminate those fields as they can 
be
looked up later if needed for some security reason.

I prefer the deleted=1 field to deleting a user's record for audit purposes, 
however
that option could easily be set in the global configuration file.  

Original comment by rdivilb...@gmail.com on 7 Feb 2010 at 12:04

[GoogleCodeExporter]

Original comment by rdivilb...@gmail.com on 8 Feb 2010 at 5:11

• Added labels: Milestone-Beta, Type-Comment-Request
• Removed labels: Type-Review

[GoogleCodeExporter]

The issue of email obfuscation can be raised at another point if the core 
developers
believe it to be important. As we have reached beta with no other objections the
issue will be closed in deference of shipping working code.

Original comment by rdivilb...@gmail.com on 9 Mar 2010 at 4:38

• Changed state: NoAction