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
|
