Improvements for Pivot Tables

[etenzy]

In my opinion a Pivot requires the $table->timestamps(); fields to support the ->withTimestamps(); function.
Also i would prefer an id field as primary key (First normal form).

Additionally a combined index between the relational id's would be a benefit for performance.

Correct me if I'm wrong

Whatever is most common, I'm fine with.

[Mythos]

IMHO a primary key out of FK1 and FK2 would be best.
Like users_groups: PRIMARY(user_id, group_id)
At the moment you can add the same entry multiple times which does not make sense to me.

[etenzy]

Thanks to @vinkla for commiting.
Currently i don't have the ability for pushing code up to github...

I've testet different cases:

PRIMARY(user_id, group_id) will end up with:
'Illuminate\Database\QueryException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxx-xxx' for key 'PRIMARY

UNIQUE(user_id, group_id) results similar...

Without checking the relation before inserting you will have two options:

1. Use an incremented id field as primary key & no UNIQUE(user_id, group_id) key and get along with duplicates
2. try/catch every save/attach on every many to many relation...

so...
The most open way is adding an incremented id field as primary key as default.

If you want to avoid duplicates just add an UNIQUE key and try/catch everything

[tabacitu]

@etenzy are you still using the package? If so, let me know what you think we should do about this, today, in 2020. I'm working on v2 #170 and would love to put this issue to rest. The package is finally getting the attention it deserves, no better time to have this if you still want it 😄

Cheers!