Maintain and Manage DB relationships

[Aggelos]

Hello, I am trying to develop a class that I will be able to maintain
my relationships dynamically.
For example I have the class employee and class company
an employ has a Foreign Key companyId which I name it $FK_companyId
What I am doing is in my constructor I define how I would like to treat
the relationships.
so:
class employee {
var $employeeId;
var $companyId;
var $FK_companyId;
function employee() {
$this->FK_companyId['onDelete'] = 'restrict';
$this->FK_companyId['onUpdate'] = 'recurse';
}

}

Thats just a thought and it is sort of working but not in every
occasion. I am not going to add the whole class as I just want to give
you an Idea of what I am thinking.

My question is if you know something that has allready been done and I
am not aware of and can be used to define the relationships and
maintain them.

Any Ideas of a class or development pattern to do something like that ?

How do you keep your referential integrity in a MySQL MyISAM table ?

Thank you guys, I know I am probably overcomplicatin g things but I want
to know if there is a common development pattern that's been used to do
that.

Angelos.

[Erwin Moller]

Re: Maintain and Manage DB relationships

Aggelos wrote:

Hello, I am trying to develop a class that I will be able to maintain
my relationships dynamically.
For example I have the class employee and class company
an employ has a Foreign Key companyId which I name it $FK_companyId
What I am doing is in my constructor I define how I would like to treat
the relationships.
so:
class employee {
var $employeeId;
var $companyId;
var $FK_companyId;
function employee() {
$this->FK_companyId['onDelete'] = 'restrict';
$this->FK_companyId['onUpdate'] = 'recurse';
}
>
}
>
Thats just a thought and it is sort of working but not in every
occasion. I am not going to add the whole class as I just want to give
you an Idea of what I am thinking.
>
My question is if you know something that has allready been done and I
am not aware of and can be used to define the relationships and
maintain them.
>
Any Ideas of a class or development pattern to do something like that ?
>
How do you keep your referential integrity in a MySQL MyISAM table ?

You don't, unless you do it by hand.
Switch to INNODB if possible.
While MyISAM is fast, it also totally sucks in functionality.
The worst thing being it pretending it understands foreign keys when you
create tables, just to simply ignore them when you insert. No warning,
nothing. (Sorry I am still a bit frustrated about that after I wasted a lot
of time before I saw some note in the documentation claiming the REFERENCES
was only parsed, not implemented and was there only as 'a reminder that you
should enforce fk yourself', or some bull like that. Really lame.)

Anyway, INNODB solves these problems.
Also have a look at mysqli instead of the old driver mysql.

INNODB + mysqli = good database that supports transactions and understands
constraints like FK.

MyISAM + mysql = poor but fast database. (It is fast because it checks
nothing, and the integrety of the data is the programmer's problem.)

Regards,
Erwin Moller

>
Thank you guys, I know I am probably overcomplicatin g things but I want
to know if there is a common development pattern that's been used to do
that.
>
Angelos.

[Aggelos]

Re: Maintain and Manage DB relationships

INNODB + mysqli = good database that supports transactions and understands
constraints like FK.
>
MyISAM + mysql = poor but fast database. (It is fast because it checks
nothing, and the integrety of the data is the programmer's problem.)

Thanks Erwin

Although that's not what I wanted at that stage to hear, is good to
know about mysqli.
I knew about the MyISAM problems but some people think it is better to
have control thru PHP rahter than in DB. And that is sort of true.
Because if MySQL throws an error that it is restricted to delete that
record, how are you going to handle it ?
Although my logic says that you should do a relationship crosscheck in
both PHP and DB, so even if you forget to handle them thru PHP you can
have them handled by a Properly deisgned DB.

Regards,
Angelos.