Ralf Eggert wrote:
>
> 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.
>
To me, "beautiful code" means that we designed it using a set of assumptions
about the structure and usage, and we don't have to support exceptions to
those assumptions. Ugly code is when we're told "make it as flexible as
possible" and we just have to do it. So no matter what solution you use,
it's going to be more or less ugly.
The EAV design that was suggested in another comment is an SQL Antipattern
as far as I'm concerned. It's not a good solution for your case because it
gives you no control over the attributes. That is, each row could
potentially have a different set of attributes. I assume you want each
client to use a fixed set of attributes, but this is unenforceable with EAV.
Creating a single table with hundreds of columns, as the union of all
columns any client might need is called "single-table inheritance." This
works, but you'd really want to enforce that attributes must be NULL except
for clients who need them. The database can't enforce this. Also, most
people find it ugly to create hundreds of columns that are NULL most of the
time.
The best option, the one I would recommend in this case, is to create a
dependent table per client who needs custom columns. The advantage is that
the database structure allows each client only their respective set of
columns (base + custom) for all rows, yet the database doesn't need to store
lots of extra data. The primary key of each client's custom table is not
auto_increment, but instead is a foreign key to the main jobs table.
Yes, you will create a lot of tables, one per client. But it's a one-time
CREATE when you set up a client, and you won't need to access the custom
tables except when that client is logged in.
You should _not_ create an attribute in the base jobs table to tell which
table references a given row. This is done to support Polymorphic
Associations, which is another SQL Antipattern. When you generate the form,
you know the client id of the current client, so you know which table to use
(name the tables by some convention mapping the client id into the table
name). You need to build the join query dynamically from this information.
Zend_Db_Select makes it pretty easy to do this safely (quoting table
identifiers, etc.).
Regards,
Bill Karwin
--
View this message in context:
http://www.nabble.com/Extendable-models-with-MySQL%2C-Zend_Db_Table-and-Zend_Form-tp18105740p18125630.html
Sent from the Zend Framework mailing list archive at Nabble.com.
