phpwebsite-developers

[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

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
>

Re: [Phpwebsite-developers] Database Issues

[Karsten D. <k.d...@tu...>]

On Tue, Mar 27, 2001 at 10:40:51AM +0200, Alain Fontaine wrote:

> Database field naming: I have adopted the following naming convention, wh=
ich
> 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, an=
d I
> personally think it's more confusing to have 15 "id" field names than
> BannerID, HostID, UserID. Just my personal idea, though.

I think this makes sense. Probably I am going to use it for my own
projects... may I? :-)

Karsten
--=20
Why do we have to hide from the police, daddy?
Because we use emacs, son. They use vi.
-----------------------------
mailto:k.d...@tu... w=B3: http://www.k-fish.de/
gpg: http://www.k-fish.de/mykeys.gpg