I have a dilemma on a design where I humbly ask your help. I'm working on
the model part of a web application (not to be understood in the "web2.0"
way, but in a more general way, where anything mounted on HTTP is a web
application) done in PHP5 following the MVC design pattern. But the strong
point is that the result must be those-who-never-RTFM-proof. But that's not
my dilemma, I only mention this so that no RoR concept or similar is thrown
into the table, that is, NO ActiveRecord.
The solution I presented is to access, and act upon, a database as if they
were PHP arrays, meaning that a table is presented as an array of records.
Here comes my dilemma. But first let me explain a bit about the scenario so
far:
* It's aceptable that some restrictions are set upon the DB structure, only
if at least the following constructions are allowed:
a) tables with only one field in the PK (usually an autonumeric int).
b) tables with a one-to-many relationship with itself, and one field PK (a
tree structure).
c) tables with a one-to-one relationship, and at most two fields in the
PK, and if there are two, one is a FK.
d) tables with a one-to-many relationship with one of the before mentioned
tables, at most two fields in the PK, and if there are two, one is a FK.
e) tables that create a many-to-many relationship between two of the
before mentioned tables, with possibly extra fields other than the fields of
the relationship, at most three fields int the PK, and if there are two or
more, two of them are FK.
* The actions than will be more used to access the data will be:
a) get one record using its PK, or a combination of FKs where it applies.
b) get one record using a unique key.
c) update or delete one record using its PK.
d) insert one record
e) loop on many records of one table, all or just one "page", or those
related to a FK.
f) order the records before the loop
My dilemma is as follows: a PHP array is a construct more restricted than a
DB table. In a PHP array the index is either an int or a string, in a table
de index can be any combination of fields. Then, my problem is how to design
coherently the indexing of the arrays that represent the DB tables.
I could index by the order as they are presented by the DB:
$DB['users'][0] is the first user from the query "SELECT * FROM users"
$DB['users'][1] is the second user from the query "SELECT * FROM users"
etc..
But this have many cons. First, without a deterministic order, the array can
change its logic order on the whim of the DB, nobody assures that the order
will be kept after a modification is made to the data, and this can be
confusing and error prone:
$name1 = $DB['users'][3]['name'];
$name2 = $DB['users'][5]['name'];
$DB['users'][3]['name'] = $name2;
$DB['users'][5]['name'] = $name1;
The last sentence may not be writing to the adequate record.
But this indexation has its pros. It can be used with a traditional for loop
(although it will prove inefficient in most cases). And the records after
and before can be easily obtained.
Another possible indexation could be by the value of the PK, but this also
have some problems. First, it can be confusing if the PK is an autonumeric
int, as this might be seen as a numeric indexation. Second, not all tables
have only one field as PK (I can ask that all tables have at least a PK, but
I can't ask that the PK is made of only one field).
But I have many pros with this strategy. I solve the actions on one record
using the PK (only if the PK is made of only one field):
$user = $DB['users'][$userid]; // get
$DB['users'][$userid] = $user; // update or insert
$DB['users'][] = $userid; // insert
unset($DB['users'][$userid]); // delete
I think I could use other than ints and strings in the array index, but I
rather stick to keeping this as seemingly equal to PHP arrays. I also could
use FK relationships to solve this, for example, if tone table has an index
made of two fields, one is an FK to another table, I could make one table
look as an array inside the other:
foreach ($DB['users'][$userid]['address_book'] as $address) {
...
}
In this case address_book refers to another table rather than a field (I
would have to ask that there are no fields with the same name). This table
has an FK to the id of the users tables and one other record working as a
PK. Accesing the array this way I have one of the values of the PK (the user
id), and I use the other as the array index.
There is also the problem with many-to-many relationships. If there was only
one table that related two tables in this way, I could do the following:
$DB['users'][$userid]['groups'] <- groups where the user belongs
$DB['groups'][$groupid]['users'] <- the users of a group
There would be a third table other than users and groups which doesn't show
up. But, what to do when there is more than one relationship table for the
same two tables? And if the relationship table also had some extra fields?
Also the delete action presents some problems:
unset($DB['users'][$userid]['groups'][$groupid]);
Am I deleting the group or the relationship between that user and group? I
believe the last is more coherent, but still it can be prone to confusion.
I could make $DB['users'][$userid]['groups'][$groupid] represent the
relationship rather than a record of groups table, where both the group data
and the relationship data are stored (I would have to ask that there are no
field names duplicated between these tables). This way I solve the problem
with the extra data, and the problem with the delete. About the possibility
that there may be two relationship tables for the same pair of tables, using
explicitly the name of the relationship table instead of the name of the
other table would solve the ambiguety (but I think it would be unusual, and
I rather keep the access through the name of the other table).
I'm sure there are more things I'm not considering, that's why I ask your
help to find them, and your opinion about what I've said up to now.
