Help with datamapper database design and structure

[eluser]Andy78[/eluser]
I am developing a sort of multiple takeaway online ordering website. I am struggling a bit with the database design especially to be optimised for datamapper orm. I have attached a ruff EER diagram to this post showing all the tables and their relationships to each other. If you look at the diagram and see how orders relate to takeaways and foods. I was planning just to store the food items for an order simply in a text field in the orders table just to keep it simple but I'm not sure if this is the best way to go about it. Looking at my diagram how should orders really be related to foods? Some kind of join table? Anything else you spot that is wrong with it let me know.

here is the image: EER

[eluser]North2Alaska[/eluser]
[quote author="Andy78" date="1334156496"]Looking at my diagram how should orders really be related to foods? Some kind of join table?[/quote]

With the idea that a food item can be on many orders and many orders have the same one food, you have a many to many relationship. Therefore, you need a table between the two. For DM it would be food_orders (or is that foods_orders?) it would have three columns; id, food_id, order_id.

And then in your food model you would have:
$has_many = array('order');

and in your order model you would have:
$has_many = array('food');

[eluser]WanWizard[/eluser]
"foods_orders". Both tables names, in alphabetical order, and separated by an underscore.

From a normalisation point of view, there are some question marks.

I don't see why pizza, kebab etc are fields in the main table. What if a merchant introduces sushi tomorrow? Same for "pizza_topping" in food. What if I ordered chinese? Then I don't want toppings, I want prawn crackers and a hot pepper sauce.

[eluser]Andy78[/eluser]
Just to be clear I have inherited this project and I am converting a half finished very poorly put together project over from raw php to codeigniter and have just tried my best to convert over the existing database so there are still some anomalies lol

The pizza, kebab etc are in the main takeaways table to determine if that takeaway sells that food type, from the existing records these fields just contain a 1 for yes or a 0 no. So for example a kebab shop in the uk will more than likely also sell Indian food and Pizza's as well. Also the client is insisting that pizza toppings be classed as food type the same as everything else so that it will work with his web-service which another developer has set-up. So the “pizza_topping” in food just contains a 1 or 0 to determine if this food item is a pizza-topping or not.

As I set this is how it was setup by the developer who initially started the project I am open to suggestions on how to make it better.

[eluser]Andy78[/eluser]
Ok I have updated the EER diagram with two new join tables foods_orders and courses_takeaways. I think I have it right but I am starting to get a little confused now lol

Based on the structure in the diagram how would I go about creating an order which has multiple food items in it and is related to a specific user and from a specific takeaway. How would I structure the save for all that in my controller?

I am also not sure if I am doing the correct thing with my courses_takeaways table? Because each takeaway will offer multiple courses containing a verity food items but the courses are not takeaway specific and can be used my multiple takeaways.

[eluser]North2Alaska[/eluser]
[quote author="Andy78" date="1334240557"]Based on the structure in the diagram how would I go about creating an order which has multiple food items in it and is related to a specific user and from a specific takeaway. How would I structure the save for all that in my controller?
[/quote]

Code:

$order = new Order();
//  Add other order information

$food = new Food();
$food->where('food_name', 'hamburger')->get();

$order->save($food);

$food = new Food();
$food->where('food_name', 'Soda')->get();

$order->save($food);

$food = new Food();
$food->where('food_name', 'French Fries')->get();

$order->save($food);

WanWizard may be able to tell us a way using an array and do the save all at once. But this is what I am doing now.

I just thought of something else:

Code:

$order = new Order();
//  Add other order information

$foods = array('hamburger', 'French Fries', 'Soda');
$food = new Food();
$food->where_in('food_name', $foods)->get();

$order->save($food->all);

That may work.

[eluser]WanWizard[/eluser]
That is indeed how I would condense it.

[eluser]Andy78[/eluser]
That was quite helpful. Should I be saving the user relationship in the same call?

[eluser]North2Alaska[/eluser]
[quote author="Andy78" date="1334253103"]That was quite helpful. Should I be saving the user relationship in the same call?[/quote]Reading the documentation, it indicates that saving in this manner saves both/all related records at the same time. You might be able to

Code:

$user = new User();
//  User stuff

$order = new Order();
// order stuff

$food = new Food();
//food stuff

//Now save it all at the same time....
$user->save(array('order' => $order, 'food' => $food->all);

I say the less code the better. :-)

[eluser]Andy78[/eluser]
This:

Code:

$user->save(array('order' => $order, 'food' => $food->all);

Doesn't work it flags up the error cannot relate user to food.

This seems to work not sure it can be condensed

Code:

function create_order(){
        
        //Get user info
        $user_id = 74;
        $user = new User();
        $user->where('id', $user_id)->get();
        
        //Get takeaway info
        $takeaway_id = 13;
        $takeaway = new Takeaway();
        $takeaway->where('id', $takeaway_id)->get();
        
        // Add other order information
        $order = new Order();
        $order->payment_type_id = 2;
        $order->order_price = 3;
        $order->delivery_cost = 3;
        $order->total_price = $order->order_price + $order->delivery_cost;
        $order->delivery_type = 1;
        $order->order_paid = 1;
        //$order->delivery_time = now();
        $order->delivery_notes = 'text';
        $order->processed = 1;
        $order->strWebServiceResponse = 'test';
        
        //get foods for order on id
        $foods = array('94', '93');
        
        $food = new Food();
        $food->where_in('id', $foods)->get();
        
        
        $order->save($food->all);
        $order->save($takeaway);
        $order->save($user);
        
        
    }