RE: [Phpwebsite-developers] Database Issues

[Alain F. <al...@va...>]

Hello,

Date format: Yes, using integers is fine as long as you don't have to deal
with dates that are before 01-01-1970 (value 0, Unix Epoch). This is
something that needs to be considered ! Also, most database engines have
built-in "date management functions" that allow easy creation of date-span
based queries, and from what I know, it's always faster to restrict the data
on the DB engine than in PHP code. I have been doing like this recently: in
my PHP classes, all dates where represented as integers, and in mySQL, they
were DATE or DATETIME fields. Then I used mySQL's functions FROM_UNIXTIME
and UNIX_TIMESTAMP to pass them in both directions.

Database field naming: I have adopted the following naming convention, which
seems to be quite appreciated by anyone who is working with my code:
* Primary keys : <tablename in singular>ID. Example: Banners table:
BannerID. Hosts table: HostID. Users table: UserID.
* Foreign keys (which are basically references) : ref<primary key name of
other table>. Like: refBannerID, refHostID, refUserID.
This naming makes it very easy for everyone to see what is going on. Why not
simply name primary keys as "id" ? Because you will end up having to type
<table name><dot>id quite often if you have joins over several tables, and I
personally think it's more confusing to have 15 "id" field names than
BannerID, HostID, UserID. Just my personal idea, though.

> -----Message d'origine-----
> De : php...@li...
> [mailto:php...@li...]De la part de
> Todd Owen
> Envoyé : mardi 27 mars 2001 03:55
> À : php...@li...
> Objet : [Phpwebsite-developers] Database Issues
>
>
> 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
>
>
>
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> http://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>