Logical Table Inheritance

[neomicron]

I have been boggling my mind with this, and I have found a couple of solutions, but they seem a bit overkill and slow - so I am hoping someone here could help out.

 

I am trying to build a MySQL database to coincide with PHP class structures (and don't suggest I just not do this - because I am... too bad :-P )

 

Take, for instance, a situation where I have three tables... I'll call them table_a, table_b, and table_c. I want it to be set up that table_c inherits (like a child object) information from table_b, which does the same from table_a.

 

Theoretically this is how it would work. Here is the table structure.

 

Table A has 2 fields. "id" and "name"

Table B has 4 fields "id" "name" "parent" and "comment"

Table C has 4 fields "id" "name" "parent" and "value"

 

Now, if I set it this way:

 

A.id = 1;

A.name = "A";

B.id = 1;

B.name = "B";

B.parent = "A";

B.comment = "Our comment";

C.id = 1;

C.name = "C";

C.parent = "B";

C.value = "Data stuff";

 

Now, when I instantiate C (ie I pull data from C) I want to have it first pull data from A, then from B, then from C. When it pulls the information from B, and there is overlap (B.name and A.name for instance) B should win out and I would get a response back with B.name. Next it would pull C and do that same. If there is not overlap, or there is a blank/non-existent field, it would not overwrite. So, if I made C, I SHOULD have the following:

 

id = 1

name = "C"

parent = "B"

comment = "Our comment"

value = "Data stuff"

 

 

Now, I have done this a few ways. For instance I could do a join twice, and have PHP dig through a mysql_fetch_array result and find the last instance of each variable. The other option I had was to use SELECT CASE repeatedly for each field of the table, and have it do the logic.. but if I say made children of children of children (multiple inheritance) those case statements would get to be pages long.

 

There HAS to be a better way that I am just not thinking of... any help?

[fenway]

Tables just don't inherit -- that's life. At some point, the work has to be down to "traverse" down the inheritance structure. Even if you were to use a UNION statement with c, b, a in the correct order, you'd still be stuck. It may be possible to do something fancy with triggers in MySQL 5, and have a series of UPDATE statements run consecutively (a,b, then c) and only run the updates if there's valid value in the field; I think this is possible now that you can refer to explicit table names in triggers, but I really don't know.