CakePHP Form Helper Database Interaction

Question

Currently I am developing a CakePHP that will list various businesses.

In the database, there is a table for businesses that lists them like so:

id | name | address | city | state | state_id | zip | url

The state column are abbreviations of states (for listing purposes) CA, AK, FL, etc and the state_id matches up with the ids in the states table:

id | name | state_abbr

I have an admin_add.ctp template with form helpers for inserting new businesses.
For entering a state for a business I am going to have a pull down that lists all the states. However, how do I make the database insert so it will know how to add the state abbreviation and state id when I submit the form to add the business?

Answer 1

Save only state_id in your businesses table, you can always recover the state_abbr whenever you need it from the states table. (So delete the state field from businesses).

Evenctually, to avoid join each time, you can always read the states table once and have a mapping state_id=>array("name"=>something,"state_abbr"=>xy) to retrieve that information faster if you need it a lot of times in the same session

Answer 2

I figured it out.
In my Business controller I have var $uses = array('business', 'state') and then in the add function I can pass all the states data to the view of the Add Business page.
Then in the view of the Add Business I set an associative array linking the state_id to the state name. Then I can pass that through $form->input('state_id', array('options' => $stateArray)).