Handle data insertion after validation

I have an application that has a form. Validation and data insertion works fine. However, it bulks out my controller. I've always been told skinny controllers.

For example:

class myController extends BaseController
{
    public function store(myRequest $request)
    {
        $company_id = Company::insertGetId([
            'name'      => $request->name, 
            'contact'   => $request->contact, 
            'email'     => $request->email, 
            'telephone' => $request->telephone, 
        ]);

       $this->handleQuestion($request->question_one, '1', $company_id->id);
       $this->handleQuestion($request->question_two, '2', $company_id->id);
       $this->handleQuestion($request->question_three, '3', $company_id->id);
       $this->handleQuestion($request->question_four, '4', $company_id->id);
       $this->handleQuestion($request->question_five, '5', $company_id->id);
       $this->handleQuestion($request->question_six, '6', $company_id->id);
       $this->handleQuestion($request->question_seven, '7', $company_id->id);

       Outstanding::create([
            'company_id' => $company_id->id, 
       ]);
    }

    public function handleQuestion($answer, $q_id, $company_id)
    {
        if( $question ) {
            CompanyAnswers::create([
                'company_id'  => $company_id, 
                'question_id' => $q_id, 
                'answer'      => $answer, 
            ]);      
        }   
    }
} 

Open in new window


The database schema has been created with a 2NF relation in mind.

companies

id (PK)
name
contact
email
telephone

Open in new window


company_answers

id (PK)
company_id (FK)
question_id (FK)
answer

Open in new window


outstanding_companies

id (PK)
company_id (FK)
question_id (FK)
answer

Open in new window


How is the application intended to work?


When the form is filled out, we create the company.
We ask seven questions - questions are not mandatory. (This is why we have a company_answers table - to avoid redundant data.) The results of the questions are stored in company_answers.
We insert the application in outstanding_companies so we know that this application is outstanding and needs attention.

My Problem


Have I totally misunderstood database normalisation and hindered myself by adding more code to handle data insertion?
If I have understood database normalisation, what is a better approach for handling the data insertion?
Is skinny controllers true? If so, where do I put data insertion logic?
JakehallasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zephyr_hex (Megan)DeveloperCommented:
In terms of question 3 (skinny controller), MS tutorials often have validation and insertion in the controller.  Personally, it seems to me to overstretch the bounds of the controller's purpose -- the controller is essentially a traffic director and should pass objects to/from the view and to/from the model layers.  So, I put my validation and database related activities (i.e. insertion) in the model.  For example, I have a method in my model class called Update(), and it handles the database updates/insertions/deletes.  And another method called Validated() which returns a boolean.  In my controller, I simply have:  if(myModel.Validated()) {myModel.Update();}, and then I handle all of the details for validation and updates in the model class.

And in terms of question 2, I much prefer the database structure you're using to ones that stuff everything into one table.  On the other hand, I've seen normalization taken too far -- when don't need a separate table for months of the year, for example, which are then linked by ID.  Those names aren't going to change and it's a relatively small list of values, so it's better, in my opinion, to just use the month name in the main table rather than break it out into a related table.  But for your purposes, I think your table structure is the right approach.

I think my answers to questions 2 & 3 should cover your first question.
1
Julian HansenCommented:
Personally I prefer to do the validation as part of the update in the model. For me the controller is as zephyr_hex describes it - just a traffic director so I do the following

$model = Model::Factory('myModal', $db);
$errors = $model->update($data);
if (empty($errors)) {
   // direct to success page
}
else {
   $view->setError($errors);
   // set errors for display and direct back to form
}

Open in new window

With respect to table structure. We have a survey package which basically asks questions captures answers and stores them.
We tried two approaches
1. Normalise the DB and store the answers in an answer table and relate back to the quesiton_id and survey_id

This worked well but it meant a lot of JOINS to get the data and present it to the engine.

2. Store the answers in the respondent record as a JSON string.

There are advantages and disadvantages with both.

The first has the advantage that you can easily pull reports on the data without having to decode JSON strings. The disadvantage is that it is more complicated to get the data to build an input screen.

The second has exactly the opposite - storing and retrieving of state information for the survey is a simple query - the results are sent in JSON back to the client as part of a JSON/AJAX request - same for saving - JSON structure is sent from the client - and simply stored.

In the end we chose option 2 - the reporting component we required was a simple export to CSV which was handled with a simple class that reads the JSON - decodes it and immediately there is an array / object structure to deal with. We cannot do database queries on the answers but then we had no intention of doing that as the analysis is done by an external stats package that requires CSV input - so nothing really lost and the coding around the state of the engine is much simpler.

In the end it is a choice that your particular application is going to make for you - what makes the most sense for your requirement.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.