Bridging users, companies and transactions

If what you want to achieve is this command:

App\User::find(1)->transactions->get();

Then why don't you structure your Database like:

• companies table => id | ....
• users table => id | company_id | ...
• transactions table => id | user_id | ...

Then add relations:

Company Model:

public function users() { return $this->hasMany(User::class); }

User Model:

public function company() { return $this->belongsTo(Company::class); }

public function transactions() { return $this->hasMany(Transaction::class); }

Transaction Model:

public function user() { return $this->belongsTo(User::class); }

Now you can have:

• Eager Load Relation: $user = App\User::find(1)->with('transactions')->get();
• Then the results are: $user->transactions;

Edit

if 1 Transaction has 1 TransactionDetail, then this table is not needed I think. Simply add the extra fields to the transactions table.

For Transaction Details, you can then have:

• transaction_details table => id | transaction_id | ... Transaction Model:

public function transactionDetails() { return $this->hasMany(TransactionDetail::class); }

TransactionDetail Model:

public function transaction() { return $this->belongsTo(Transaction::class); }

Then the one you had before becomes:

• Eager Load Relation: $user = App\User::find(1)->with('transactions.transactionDetails')->get();
• Then the results are: $user->transactions; To get the details:

@foreach($user->transactions as $transaction)
    {{ $transaction->transactionDetails->id; }}
@endforeach

Hi xmarks,

Thanks for a quick reply. You make it seem so easy. Having somebody look at this cold, certainly helps.

1. On linking company with user Yes, I can add a company_id to the users table instead of my current belongsToMany intermediate table. One "downside" then is that not every user is part of a company, so the company_id will be NULL for some (if not most). But I don't see any actual objections to do so. Or?

2. Getting company transactions Given this I assume that - they are new for me - I can get the transactions for a company through a hasManyThrough relation (in Company model):

public function transactions() 
{ 
    return $this->hasManyThrough(
        Transaction::class, 
        User:class,
        'company_id',
        'user_id',
        'id',
        'id',
    );
}
 
$companyTransactions = App\Company::find(1)->with('transactions')->get();

3. Getting transaction details Theoretically I can then, through the hasManyThrough, get to the details, also for a company.

$companyTransactionDetails = App\Company::find(1)->with('transactions.transactionDetails')->get();

Or indeed, but I'll have a think about that, add details to the same row in Transactions if indeed I expect a 1:1 relation on these tables.

Thanks.

1.

I think that the only case where you should require a PivotTable company_user is if a User can belong to more than 1-company. Otherwise, it wouldn't hurt to keep the relation on the users Table.

2.

Sure, if that works for you. I do not have much experience with hasManyThrough as I have yet to make use of it, but I checked the documentation and it seems correct. You could probably simplify it since you are currently using Laravel Naming Conventions:

public function transactions()
    {
        return $this->hasManyThrough(Transaction::class, User::class);
    }

3.

Again same with the relation between users and companies. If you are certain that the relation is 1:1, then you should keep things in the same Table. I think systems (Servers, Storage and Programs) are now powerful enough to not having to worry about null-assigned fields taking up too much space.

I would think that you owe it to yourself and whoever will take care of your code later to keep things as simple and as easy to manage as possible. But again this would be a personal opinion.

I would only use a PivotTable (or Intermediate Table) for true cases of Many-To-Many Relations, like "Tags / Categories etc..." where I would check if the data has already been used before:

• If Yes => assign the same data
• If No => create the new data and assign it / Or Data does not exist exception

Then this data, having the same ID, can be used to fetch all different Models where it has been assigned to. (Like for example, creating a Search for Posts, from certain Categories or Tags / Or Finding all Companies of a User, if more than 1 in your example)