Database setup question

Another way is to set polymorphic relation with a userable user, for n:n relations with stores and companies.

Also there is a package for a triple pivot table.

Hey @bestmomo thanks for your reply. Im not a to big fan for using a package for this. somehow it limits my knowledge solving this problem.

I might look into polymorphic but im not sure that is the way to go for this :) Need to say my experience with polymorphic relations is zero but if there is no better option i will look into it!

@kazehaya

Triple pivot is a headache and this package is nice for that.

Polymorphic is perfect for simple situations.

@bestmomo Yep it seems there i not an easy solution for this.. Is it an idea to use multiple two way pivot tables or is that bad database design?

I might consider using the package if nobody has an other solution ;)

@kazehaya multiple 2 ways pivots are not bad design. Otherwise how to manage that ? Alternative is polymorphic relation.

@bestmomo I might ask to much from you but can you help me set up the database structure with polymorphic relation and explain a little bit of the structure.

That would help me so much and maybe others who have the same question as me :)

@kazehaya

Something like this :

Schema :

companies
    id - integer
    ...

stores
    id - integer
    ...

users
    id - integer
    ...

userables
    user_id - integer
    userable_id - integer
    userable_type - string

Store (same for Company) :

class Store extends Model {

    ...
    public function users()
    {
        return $this->morphToMany('App\User', 'userable');
    }

}

User :

class User extends Model implements AuthenticatableContract, CanResetPasswordContract {

    ...
    public function stores()
    {
        return $this->morphedByMany('App\Store', 'userable');
    }

    public function companies()
    {
        return $this->morphedByMany('App\Company', 'userable');
    }

}

@bestmomo Thank you for explaining! Though, i don't quite understand what "userables" is being used for? What does it do (userable_id and userable_type)? Secondly, am i correct that in this setup, the user is connected to the company and the store, but the store not connected to the company? Or is that being undone with the userables table?

@kazehaya polymorphic "userable" pivot is only to avoid the 2 pivots : "company_user" and "company_id".

You 3 ways pivot between the 3 tables is another story. And I must say I dont really understand your logic...

@bestmomo I have worked on the polymorphic example and got the idea now:) currently working it out but not expecting any big problems^^

Thanks for helping Im pretty sure i can achieve what i want now thanks to your example! :D

@bestmomo Im wondering how i can store this i understand the logic now already a lot better. When i create a user and save the companies, stores and shops arrays into the userables table how can i do that?

I now have $user = User::create($request->except('company_list', 'stores', 'shops');

i guess i then need to do something like: $user->companies()->create('??');

@kazehaya look at this part of the documentation to know how to save a related model.

$company = new Company(...);

$user = User::create($request->except('company_list', 'stores', 'shops'));

$company = $user->companies()->save($company);

Thanks i got that part working now!

Only one problem is that when i want to save my shops im getting this array back (the id's of shops): array:5 [▼ 0 => "1" 1 => "2" 2 => "1" 3 => "2" 4 => "1" ]

And in my userables table it saves it as 2 rows with each the id's (1 and 2). My problem is that there is no connection between the store and the shop. (Since each store can have the same shops). So i don't understand how i can create the connection from the shop id to the corresponding store id, in the userables table.

I hope that makes any sense at all

There is no relation between stores and shops in "userable" that just set relation between users and other entities.

there is a relation i guess, im creating an admin panel to save a user, i choose to which companies the user belongs to and the stores that belongs to the given companies. when i have done that i choose the shops they have acces to with the given stores.

Hope this cleans up things a little.

I think you need to clarify your schema.

Okey this can be a long post :)

I have an admin panel:

• 1. First of all i create a company.
• 2. Then i create a store and bind it to a single company.
• 3. Then i create a shop
• 4. After creating shops i can bind the shops to the store.
• 5. Now when i create a user and choose multiple companies it shows the stores that belongs to the companies.
• 6. Now we need to choose from the store the shops that belongs to the stores. (remember the modules are build up thanks to step 4)

You need to see it like this: there is a big company that holds multiple stores and those stores have multiple little shops. When saving a user i need to see to which companies they belong as well to which stores an shops.

Now i have these tables:

companies table

• id
• name

stores table

• id
• company_id
• name

shops table

• id
• name

shop_store table

• store_id
• shop_id

users table

• id
• username
• password

userables table

• user_id
• userbable_id
• userable_type

@kazehaya

Does a shop belongs only to 1 store ? As I see a shop_store_table I presume answer is "no" but I wonder.

A shop can belong to multiple stores, kind of awkward but thats the way it is ;)

As I understand it you dont need to relate directly user to a company as if you know he belongs to a certain store you can get the company with it.

So you need to relate user (with a polymorphic n:n) to stores and shops as you have a curious relation between stores and shops ^^.

I get the first thing, btw good finding there!

Still wondering how to see which shops belongs to which stores in my userables table :/

sorry for bothering you so much :P

I dont know why you want to know which shops belongs to which stores with users. There is a structure with stores and shops, even without users ^^

There is something I dont understand somewhere...

haha when i create the user the user can also login the admin panel and thats why everything needs to be connected to the specific user :)

he is going to see content specific to his options

So a user create a relation between store and shop ? It's a bit weird...

Well the user that is created logs in and create new users but can only choose to the store and shops he self has acces to. I am at master level and can choose of all companies and stores etc. When the created user logs in he has admin rights and can only create users that are bind to the same companies and stores he has acces to.

I know it sounds confusing, maybe it helps if you thinks of it like this: store = projects and shops = modules. I think that makes more sense but its not what my project is about ;)

Do you mean that a user is related to many couples store/shop ?

Yes exactly, i have tried to get it working in the meantime but somehow i need to figure out a good way to get the shops unique or combined to the store when sending the form. otherwise it wont know when sending the form what shop id belongs to what store. Any suggestions for that? or maybe an other workaround to achieve this.

This is what im getting from the form im sending:

"stores" => array:3 [

    0 => "1"

    1 => "2"

    2 => "3"

]

 "shops" => array:4 [

    0 => "1"

    1 => "2"

    2 => "2"

    3 => "1"

  ]

And thanks you so much for helping me out on this, you already have invested so much time in helping me! Means a lot to me :)

You have this table :

shop_store table
    id
    store_id
    shop_id

So you still have a table where to get these couples shop/store. So I think to a many to many relation between users and this table :

shop_store_user table
    user_id
    shop_store_id

I was thinking the same! One thing i don't really like is the auto increment id in the shop_store table. I think that id can build up quick not sure if thats a really big problem but just a concern.

So then i could use that auto increment id from the shop_store table into the userables table right? :)

If you do that you dont need the userables table, but only the shop_store_user pivot, because you just relate users to couples store/shop. So for each user you can know : stores, shops, and companies (with stores).