[Phpwebsite-developers] Database Issues

[Todd O. <to...@da...>]

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