Our POS software has a price schedule ID attached to a user. Then the product prices are stored on a separate related table from the product table. The product prices table has a product ID, price level ID, and a price. This allows for groups of users to get a certain price.

Then there is a price exception table that allows you to set a price for a specific item for a specific customer. It has a customer ID, product ID, and price columns.

You seem like you are very close.

Is it literally fixed custom price for each user? It seems a bit unreal, imagine you need to manage price changes for example.

I'd rather see this as a % discount a user can get, then it's pretty easy.

Your current solution definitely requires you to make sure the price is always eager loaded.

jarektkaczyk said: Your current solution definitely requires you to make sure the price is always eager loaded.

I am considering discounts but some prices need to be fixed no matter what the base price is...

Eager loading. How do I achieve that in this situation ? I have used eager-loading for relations but how do I define one here ?

pickupman said:

Our POS software has a price schedule ID attached to a user. Then the product prices are stored on a separate related table from the product table. The product prices table has a product ID, price level ID, and a price. This allows for groups of users to get a certain price.

Then there is a price exception table that allows you to set a price for a specific item for a specific customer. It has a customer ID, product ID, and price columns.

You seem like you are very close.

I thought of something similar but I was unable to bring this exception into picture. How do you manage to bring that into your design ?

You could do something like:

public function getAlternativePrice(User $user)
{
    return $this->hasOne("name of your class", "product_id")->where("user_id", "=", $user->id);
}

public function getFinalPrice(User $user)
{
    $alternativePrice = $this->getAlternativePrice($user)->first();
    return ( $alternativePrice ) ? $alternativePrice->price : $this->price;
}

@Ruk33, How do I eager load it ?

If you need several users, you will have to change the method a little bit:

public function getAlternativePrice()
{
    return $this->hasOne("name of your class", "product_id");
}

Then:

<product query>->with('getAlternativePrice');

As the question has expanded from your original question, our product price schedule table has actually one more column of price type. It is an ENUM column with F(ixed), P(ercent off), M(arkup from cost). When price for an item needs updated, there is a function that runs that will update products_prices based on the price schedule table. I run this function after importing a CSV from a vendor with pricing updates. I set the update to run on products for that vendor id.

Our price exception table doesn't not get updated automaticlly during this update. The price exception will always stay the same until it is manually updated, unless a expiration date has been set.

mcraz said:

jarektkaczyk said: Your current solution definitely requires you to make sure the price is always eager loaded.

I am considering discounts but some prices need to be fixed no matter what the base price is...

Eager loading. How do I achieve that in this situation ? I have used eager-loading for relations but how do I define one here ?

OK, imagine scenario:

Product has its base price, user can have % discount and/or fixed price for each product We use built-in Auth for authentication

We'll create a pivot table:

product_user: id, product_id, user_id, timestamps, price decimal (8,2), discount decimal (2,2)

Then we need a relation between the product and (logged in) user (for eager loading):

public function users()
{
   $userId = (Auth::user()) ? Auth::user()->getKey() : 0;
   return $this->belongsToMany('User')->withPivot('price', 'discount')->where('user_id', $userId);
}

This looks a bit odd for a m-m relation, for we need only one user (logged-in). Still, it can't be any other Eloquent relation if we want to use pivot data (without defining additional models).

Now, finally we want to do this:

$product = Product::find($id);
$product->price;

We should get:

1. user defined price (if pivot price found)
2. discounted price (if pivot discount found)
3. product's base price

This is trivial for a single product, but as stated before it would be overkill (n+1 query issue) when loading a list of products. That is why we need that helper users relation:

$products = Product::take(10)->get(); // we are going to load pivot data here!
foreach ($products as $product)
{
   $product->price; // works as expected
}

You can achieve that with this simple setup: http://laravel.io/bin/3jWMP

Think of improvements that are still possible in that code.

Thanks @jarektkaczyk , I will try it out today.