Storing table references in text field (for Agile Toolkit)

I have been experimenting with RDB design lately, and I was wondering about storing items in a field that can have more than one value:

 开发者_StackOverflow   CARS        Color_avail
  1  corvette    1, 2, 3        <<<<<<<
  2  ferrari      2
  3  civic        1


    COLORS 
  1  red
  2  White 
  3  black 

so on CRUD I would like to add more than one item via a drop down / checkboxes or something that would hold multiple values.

I can see the benefit of displaying the output like this in a form, but do you really want to store it like this in the database ?

For example with a datamodel that holds a comma separated list as in your example, what SQL would you use to identify all the cars available in white ?

The traditional way to hold a many to many relationship like this is to use an additional table e.g. you have a separate table that holds CAR_COLOUR with the following contents

 CAR      COLOUR
 1        1
 1        2
 1        3
 2        2
 3        1

So now you can easily query things like, get a list of all cars and colours

SELECT CAR, COLOUR
  FROM CARS CA, 
       COLOUR COL, 
       CAR_COLOUR CACOL
 WHERE CA.CAR=CACOL.CAR
   AND CACOL.COL=COL.COLOUR

OR if you just want the white cars, add the following to the WHERE clause

   AND COL.COLOUR='White'

an index on the id fields and on both fields in CAR_COLOUR will mean you get great performance even if you have thousands of rows whereas putting them all in a comma separated list in a single field will mean you have to use substr or like which would prevent the use of indexes and mean as the amount of data grows, the performance will degrade rapidly.

Storing relations in the coma-separated list makes sense in some senses. You don't need commas though. There are 2 existing controls which can help you with that.

Displaying list of values with checkboxes in a form:

$form->addField('CheckboxList','corvette')->setValueList($array);

(you can populate array through $model->getRows() although I think it needs to be associative. You can probably join them with var_dump and foreach).

Your other options is to use a hidden field with selectable grid.

$field = $form->addField('line','selection');
$grid = $form->add('MVCGrid');
$grid->setModel('Colors',array('name'));
$grid->addSelectable($field);

$form->addSubmit();

To hide the actual field, you can either use "hidden" instead of "line" or use JavaScript to hide it:

$field->js(true)->hide();

or

$field->js(true)->closest('dl')->hide();

if you need to hide markup around the field too.