[Phpwebsite-developers] Re: Phpwebsite-developers digest, Vol 1 #34 - 11 msgs

[Matthew M. <mcn...@tu...>]

Interger dates are easier, and I happen to be using that format in my 
coding. Keep in mind however that in changing the schema of the database 
will require a conversion from the old table structure. So when developing 
the new code with the clearer table names (which I feel IS needed), a file 
needs to convert all the old data into the new table format.

Matt McNaney

> I've been reviewing the database table schemas, since I think they need
> some significant reform and consistency.  During this review I noticed
> that timestamps are done as both 'datetime' and 'timestamp' (aka
> INTEGER) data types.
> 
> Although the DATE format is consistently 2001-03-26 across many
> database implementations, the DATETIME format is not.  I know ADODB
> specifically addresses this issue.  I propose we use the DATE data type
> where it is needed, like calendar events, but use INTEGER timestamps
> everywhere else. This has several advantages in my opinion.
> 
> 1.  The INTEGER data type is very efficient in storage space and
> difference calculations.
> 2.  PHP has many date and time functions that give us significant
> flexibility.
> 3.  INTEGERs are standard across all database implementations (and they
> record the seconds too).


> 
> I also think the polls, URL referrals, banners and the like should be
> broken out into plug-in modules and removed from the core.  I believe
> the table schema should also be standardized and made clearer such as
> the following:
> 
> 1.  All tables rows have an integer id already, but the field name
> should be 'id' in all cases, since it's the primary key--not sid for
> id's in the session table and cid for id's in the category table.
> 2.  Foreign keys, although not enforced in the code or database, are
> implemented.  A standard naming convention should be employed, such as
> the following:
> 
> CREATE TABLE banner (
>   id                  INT DEFAULT '0' NOT NULL auto_increment,
>   category_id INT DEFAULT '0' NOT NULL, ...  (really a foreign key)
> 
> Joins would look like "SELECT banner.id FROM banner WHERE
> banner.category_id = category.id".  This looks very consistent and
> documents itself.
> 
> --------
> 
> This consistency (and database abstraction as well) comes at the price
> of lots of PHP code needing to be reviewed and updated.  I believe we
> will be much better off in the long run if we do this now instead of
> later.  If there is significant support for this, then I will release a
> new table schema for review and will work with those implementing the
> db abstraction layer to update the queries.
> 
> There's more, but that will have to wait until tomorrow.  My caffeine
> level is getting low.
> 
> --Todd Owen