Relationships of models (Laravel 5.2)

My tables (Mysql DB):

// Stores Table

CREATE TABLE IF NOT EXISTS `app_beta`.`stores` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`))

// Items Table

 CREATE TABLE IF NOT EXISTS `app_beta`.`items` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `user_id` INT UNSIGNED NOT NULL,
 `title` TEXT NOT NULL,
 `content` LONGTEXT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_items_user_id`
  FOREIGN KEY (`user_id`)
  REFERENCES `app_beta`.`users` (`id`))

// Products Table

CREATE TABLE IF NOT EXISTS `app_beta`.`products` (
`id` INT UNSIGNED NOT NULL,
`reviews` DECIMAL(7,1) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `fk_products_id`
 FOREIGN KEY (`id`)
REFERENCES `app_beta`.`items` (`id`))

// Product_Store Table

CREATE TABLE IF NOT EXISTS `app_beta`.`products_stores` (
`product_id` INT UNSIGNED NOT NULL,
`store_id` INT UNSIGNED NOT NULL,
`price` DECIMAL(7,2) NOT NULL,
`url` VARCHAR(255) NOT NULL,
 CONSTRAINT `fk_products_store_product_id`
 FOREIGN KEY (`product_id`)
 REFERENCES `app_beta`.`products` (`id`),
 CONSTRAINT `fk_products_stores_store_id`
 FOREIGN KEY (`store_id`)
 REFERENCES `app_beta`.`stores` (`id`))

// Offers Table

CREATE TABLE IF NOT EXISTS `app_beta`.`offers` (
`id` INT UNSIGNED NOT NULL,
`store_id` INT UNSIGNED NOT NULL,
`price` DECIMAL(7,2) NULL,
`url` VARCHAR(255) NOT NULL,
`start_date` DATE NOT NULL,
`end_date` DATE NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_offers_store_id`
FOREIGN KEY (`store_id`)
REFERENCES `app_beta`.`stores` (`id`),
CONSTRAINT `fk_offers_id`
FOREIGN KEY (`id`)
REFERENCES `app_beta`.`items` (`id`))

Add. Info:

My tables are migrated. Just to clarify... the products and offers inherit from the items table. If the item is not created I can not add products and offers.

The product can have the title, summary, content, category etc... same for the offer.

The product can be on 1-many stores
The offer can be only on 1-1 store.

If I'm wrong LET ME KNOW!

** Please, I want someone to help me creating the relationships between the Item model, product and offer. Can i use polymorphic relations? **

Models DONE:

class Store extends Model
{
public function offers()
{
    return $this->hasMany('App\Offer');
}

public function products()
{
    return $this->hasMany('App\Product');
}
}

class Product extends Model
{
public function stores()
{
    return $this->belongsToMany('App\Store');
}
}

class Offer extends Model
{
public function store()
{
    return $this->belongsTo('App\Offer');
}
}

using php artisan tinker, all works nice!

namespace App

$user = new User
$store = new Store

$item = new Item
$item->id = 1
$item->user_id = 1
$item->title = 'test'
$item->content 'test'
$item->save();
true

$item2 = new Item
$item2->id = 2
....
true

$product1 = new Product
$product1->id = 1 (FK item->id)
$product1->reviews = 5
$product1->save()
true 

$offer1 = new Offer
$offer1->id = 2 (FK item->id)
$offer1->store_id = 1

...
true

I'll add later a function to attach product to one or many stores (products_stores table).

Thanks.

This is how I think you can have a good start...

First of all, your model and migration can handle all it.

There is for relationship:Laravel 5.2 Relationship There is for migration:Laravel 5.2 Migration

So there you create your migration:

Schema::create('stores', function (Blueprint $table) {
    $table->bigIncrements('id')->unsigned();
    $table->string('name', 50);
    $table->timestamps();
});

Schema::create('items', function (Blueprint $table) {
    $table->bigIncrements('id')->unsigned();
    $table->bigInteger('user_id')->unsigned();
    $table->foreign('user_id')->references('id')->on('users');
    $table->text('title');
    $table->longText('content');
    $table->timestamps();
});

Schema::create('products', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('store_id')->unsigned();
    $table->foreign('store_id')->references('id')->on('stores');
    $table->decimal('reviews', 7,1);
    $table->timestamps();
});

Schema::create('offers', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('store_id')->unsigned();
    $table->foreign('store_id')->references('id')->on('stores');
    $table->bigInteger('item_id')->unsigned();
    $table->foreign('item_id')->references('id')->on('items');
    $table->decimal('price', 7,2);
    $table->string('url', 255);
    $table->dte('start_date');
    $table->dte('end_date');
    $table->timestamps();
});

So, once you did this, you can make your relationship onto your model. This way you don't need all the "between" tables. When you will use associate(), Laravel will create the link for you. This way you can do something like this: $offer->store()->name to get the name of the store of the current offer. Take a look:

Into Store's model

public function products()
{
    return $this->hasMany(Product::class);
}

public function offers()
{
    return $this->hasMany(Offer::class);
}

Into Offer's model

public function store()
{
    return $this->belongsTo(Store::class);
}

This way, You create a one-to-many relation. Has I said, $offer->store() will retrieve the store of the offer. $store->offers()->get() will retrieve all offer of the store.

Hope it help.

EDIT

There is one only problem with what I said. The n + 1 problem. So like it explain there(search google "laravel n+1 problem" and pick the link to laracast) (can't put it as a link, not enough reputation) , when you call things like I said, the script will do 2 query. When you use a foreach() loop, it'll have as much loop +1 query. I suggest you to do things like that

$offers = Offer::with('store')->all();

This way you'ill have only 1 query and you will still able to do

$offer->store;

without doing another query.

When you use $model = Model::with('something')->all();, the query will fetch data from 2 table and return the result with an array into an array. Like this:

offers {
    [0]:{a,b,c,d,e, store{a,b,c,d,e}}
    [1]:{a,b,c,d,e, store{a,b,c,d,e}}
    [2]:{a,b,c,d,e, store{a,b,c,d,e}}
    [3]:{a,b,c,d,e, store{a,b,c,d,e}}
}

You can use the opposite:

$stores = Store::with('offers')->all();

So you can use:

$store->offers[i]->somthing;

Because the array will look like this:

stores {
    [0]:{a,b,c,d,e, offers{
                        [0]:{a,b,c,d,e}
                        [1]:{a,b,c,d,e}
                        [2]:{a,b,c,d,e}
                        [3]:{a,b,c,d,e}
                        }}
    [1]:{a,b,c,d,e, offers{
                        [0]:{a,b,c,d,e}
                        [1]:{a,b,c,d,e}
                        [2]:{a,b,c,d,e}
                        [3]:{a,b,c,d,e}
                        }}
    [2]:{a,b,c,d,e, offers{
                        [0]:{a,b,c,d,e}
                        [1]:{a,b,c,d,e}
                        [2]:{a,b,c,d,e}
                        [3]:{a,b,c,d,e}
                        }}
}