I have a personal project I'm planning and I came to a small hurdle.
I want to have an item with a price that will be the default for all
clients/users. However, in my business I have some clients that are
grandfathered in to some special pricing. In the case of these
grandfathered in cases, I'll manually plug their special price in my
admin section. Then all they will see is their special pricing while the
regular users/clients see the default price.
My current thoughts are to have a separate table called custom_pricing
with fields product_id, client_id, price and then tap into that. The
product table would have product_id and price.
What is the best and simplest way to design and implement this?
I have a personal project I'm planning and I came to a small hurdle.
I want to have an item with a price that will be the default for all
clients/users. However, in my business I have some clients that are
grandfathered in to some special pricing. In the case of these
grandfathered in cases, I'll manually plug their special price in my
admin section. Then all they will see is their special pricing while the
regular users/clients see the default price.
My current thoughts are to have a separate table called custom_pricing
with fields product_id, client_id, price and then tap into that. The
product table would have product_id and price.
What is the best and simplest way to design and implement this?
I would have a table called pricing and have a column that had the
customer_id and thus you might have 2 (or more) prices for an item but
only one for a specific customer or default to the one that is not tied
to a specific customer.
Hi Craig!
I don't think this would work as there are quite a number of clients
(over 25) who currently have a different price per product. In the
future there might be additional clients with custom pricing depending
on how much volume they push.
If I were to implement your suggestion I would have to have 25+ fields
(pricing_1, pricing_2, pricing_3, etc.) which I don't think is the
correct approach for this. Did I misunderstand your suggestion?
> I would have a table called pricing and have a column that had the
> customer_id and thus you might have 2 (or more) prices for an item but
> only one for a specific customer or default to the one that is not tied
> to a specific customer.
Hi Craig!
I don't think this would work as there are quite a number of clients
(over 25) who currently have a different price per product. In the
future there might be additional clients with custom pricing depending
on how much volume they push.
If I were to implement your suggestion I would have to have 25+ fields
(pricing_1, pricing_2, pricing_3, etc.) which I don't think is the
correct approach for this. Did I misunderstand your suggestion?
yes, you misunderstood... sorry if I wasn't clear enough. Something like
this, definitely not tested and perhaps you can get it down to a single
query.
Class Pricing
has_one :customer
belongs_to :item
id
price
customer_id
def self.item_price(item, customer)
if Price.find(:first, :conditions => ["item_id = ? AND customer_id
= ?", item, customer']) then
return Price.find(:first, :conditions => ["item_id = ? AND
customer_id = ?", item, customer']).price
else
return Price.find(:first, :conditions => ["item_id = ? AND
customer_id = NULL", item]).price
end
end
Craig
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
AH! Okay - I think I got it.
So you would just remove the price field from the item table altogether
and just put it in its own table.
Any tips or resources on the best way on how to do this? Still a bit of
a rails newbie. Thanks!
Craig White wrote:
> yes, you misunderstood... sorry if I wasn't clear enough. Something like
> this, definitely not tested and perhaps you can get it down to a single
> query.
>
> Class Pricing
> has_one :customer
> belongs_to :item
>
> id
> price
> customer_id
>
> def self.item_price(item, customer)
> if Price.find(:first, :conditions => ["item_id = ? AND customer_id
> = ?", item, customer']) then
> return Price.find(:first, :conditions => ["item_id = ? AND
> customer_id = ?", item, customer']).price
> else
> return Price.find(:first, :conditions => ["item_id = ? AND
> customer_id = NULL", item]).price
> end
> end
>
> Craig
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
AH! Okay - I think I got it.
So you would just remove the price field from the item table altogether
and just put it in its own table.
Any tips or resources on the best way on how to do this? Still a bit of
a rails newbie. Thanks!
> I would have a table called pricing and have a column that had the
> customer_id and thus you might have 2 (or more) prices for an item but
> only one for a specific customer or default to the one that is not tied
> to a specific customer.
Hi Craig!
I don't think this would work as there are quite a number of clients
(over 25) who currently have a different price per product. In the
future there might be additional clients with custom pricing depending
on how much volume they push.
If I were to implement your suggestion I would have to have 25+ fields
(pricing_1, pricing_2, pricing_3, etc.) which I don't think is the
correct approach for this. Did I misunderstand your suggestion?
----
yes, you misunderstood... sorry if I wasn't clear enough. Something like
this, definitely not tested and perhaps you can get it down to a single
query.
Class Pricing
has_one :customer
belongs_to :item
Should that be belongs_to: customer rather than has_one, along with
customer has_many pricings, and item has_many pricings?
I would have used class Price rather then Pricings but that is a
matter of personal preference.
Colin
id
price
customer_id
You will need an item_id also
def self.item_price(item, customer)
if Price.find(:first, :conditions => ["item_id = ? AND customer_id
= ?", item, customer']) then
return Price.find(:first, :conditions => ["item_id = ? AND
customer_id = ?", item, customer']).price
else
return Price.find(:first, :conditions => ["item_id = ? AND
customer_id = NULL", item]).price
end
end
This could be done as a named scope which might make for a neater solution.
>> Craig White wrote:
>>
>> > I would have a table called pricing and have a column that had the
>> > customer_id and thus you might have 2 (or more) prices for an item but
>> > only one for a specific customer or default to the one that is not tied
>> > to a specific customer.
>>
>> Hi Craig!
>>
>> I don't think this would work as there are quite a number of clients
>> (over 25) who currently have a different price per product. In the
>> future there might be additional clients with custom pricing depending
>> on how much volume they push.
>>
>> If I were to implement your suggestion I would have to have 25+ fields
>> (pricing_1, pricing_2, pricing_3, etc.) which I don't think is the
>> correct approach for this. Did I misunderstand your suggestion?
> ----
> yes, you misunderstood... sorry if I wasn't clear enough. Something like
> this, definitely not tested and perhaps you can get it down to a single
> query.
>
> Class Pricing
> has_one :customer
> belongs_to :item
Should that be belongs_to: customer rather than has_one, along with
customer has_many pricings, and item has_many pricings?
I would have used class Price rather then Pricings but that is a
matter of personal preference.
>> Craig White wrote:
>>
>> > I would have a table called pricing and have a column that had the
>> > customer_id and thus you might have 2 (or more) prices for an item but
>> > only one for a specific customer or default to the one that is not tied
>> > to a specific customer.
>>
>> Hi Craig!
>>
>> I don't think this would work as there are quite a number of clients
>> (over 25) who currently have a different price per product. In the
>> future there might be additional clients with custom pricing depending
>> on how much volume they push.
>>
>> If I were to implement your suggestion I would have to have 25+ fields
>> (pricing_1, pricing_2, pricing_3, etc.) which I don't think is the
>> correct approach for this. Did I misunderstand your suggestion?
> ----
> yes, you misunderstood... sorry if I wasn't clear enough. Something like
> this, definitely not tested and perhaps you can get it down to a single
> query.
>
> Class Pricing
> has_one :customer
> belongs_to :item
Should that be belongs_to: customer rather than has_one, along with
customer has_many pricings, and item has_many pricings?
I would have used class Price rather then Pricings but that is a
matter of personal preference.
----
that's an interesting question and probably reflects on the fuzziness of
my understanding of the difference between belongs_to and has_one. While
I can clearly see that the intent is that the difference is which table
has the referencing id, I tend to think of belongs_to as a 'must' and
has_one as a 'may' and in my original answer, I was contemplating a
price for each item that was not associated with any customer. If it
were left to me, I would probably create a 'retail' customer and
actually indicated that in my follow up answer.
----
The relationships are has_many and belongs_to or has_one and
belongs_to. In both cases the belongs_to is the one with the foreign
key. In this case since Customer has_many Prices, it must be Price
belongs_to Customer.