Hello Ralf, I see here two data-related design issues which are not necessary related to the Zend Framework as such.
1) a table with _hundreds_ of columns looks a bit weird to me because a table is a relation of many attributes, or represents some business entity object; normally such super-object will need more granularity 2) still, there is technically no problem to have a table with many columns; what you want to record here related to your clients is the relation "a column can be owned by one or more clients" i.e. many-to-many relation? so you could create another table, (colname, client_id) to manage that. (if n:1 then colname column has to be unique; vice versa, a client will still be able to have many columns) best regards, Peter Fiksman Software-Architekt ++++++++++++++++++++++++++++ pf-webservices Grantham-Allee 20 53757 Sankt Augustin Tel.: +49 (0) 2241 397 2135 Fax: +49 (0) 2241 397 2139 Mobil: +49 (0) 177 976 3257 Email: [EMAIL PROTECTED] Web: http://pf-webservices.de Ust-Id: DE223139117 ----- Original Message ----- From: "Ralf Eggert" <[EMAIL PROTECTED]> To: "Zend Framework General" <[email protected]> Sent: Wednesday, June 25, 2008 8:12 AM Subject: [fw-general] Extendable models with MySQL, Zend_Db_Table and Zend_Form > Hi, > > right now I am thinking about a problem with extendable models. To > explain this a bit, please think about a "jobs" table. This table has > some base columns like id, date, title, description and status. No > problem at all to build a model class with Zend_Db_Table and to build a > form with Zend_Form. > > Now, there are a couple of clients which need to extend this "jobs" > table with custom columns. For example client A wants to add a location > and a requirements column and client B wants to add the salary and a > services column. When client A wants to create or edit a job, only the > base columns and "his" columns should be displayed in the form and the > data should be saved in the "jobs" table. This system should be as > flexible as possible and the site administrator should be able to add > columns for each client whenever he wants to. > > My problem is now how to implement this with Zend_Db_Table and > Zend_Form. I think it is not such a good idea to add each column to the > "jobs" table because there could be hundreds of columns in future. > > Another idea was to have one "additional" column of type "text" in > "jobs" with can hold a serialized array with the additional data for > each clients job. But then some of these extra fields will be needed for > selecting and sorting the jobs of each client, which will be almost > impossible when using a MySQL text column. > > Then I was thinking of an extra table for each client, say "jobs_a" and > "jobs_b" which can be accessed and amended by the site administrator. > These extra tables could be joined with Zend_Db_Table and I could work > with merged Zend_Config objects to work with Zend_Form. But this will > result in a lot of tables in the database. > > Does anybody have some suggestions how to handle this problem? Are there > any best practices or design patterns for this? > > Thanks and best regards, > > Ralf
