Re: Design Dilemma - Database Data Abstraction

""Martin Alterisio"" <malterisio777@gmail.com> wrote in message news:52dbac0f0704070949tf516afal460e58d0ed630a50@mail.gmail.com... >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. > I think you ought to take a look at http://www.tonymarston.net/php-mysql/databaseobjects.html and http://www.tonymarston.net/php-mysql/databaseobjects2.html which explain the approach which I have adopted. You can download a working sample from http://www.tonymarston.net/php-mysql/sample-application.html. If you feel REALLY adventurous you can download its big brother from http://www.radicore.org/ In this the data access object handles all communication with the database, and the business objects deal with nothing but PHP arrays. -- Tony Marston http://www.tonymarston.net http://www.radicore.org