database design question

Dan Telschow dan_tel at hotmail.com
Thu Sep 18 13:14:46 MDT 2008


You could add an ID to the Product Option table and include that in the 
mapping table (ProductOutlet):

	ProductOutlet:
	   FK to Product
           FK to Outlet
	   FK to ProductOption
	   price (overrides default price)
	   quantity

If you do it this way, I'm not sure you need the FK to product in the 
ProductOutlet table since the FK to ProductOption would point to the 
product.  You could keep it for convenience.

James Lance wrote:
> yes.  Really the main differences between options at the outlet level
> is going to be inventory.  So amazon could have 5 size 11 air jordan's
> and ebay could have 10 size 11 air jordan's.
>
>
>
> On Thu, Sep 18, 2008 at 12:29 PM, Dan Telschow <dan_tel at hotmail.com> wrote:
>   
>> Are we to assume that options can be different between Product Outlets?
>>
>> James Lance wrote:
>>     
>>> I thought I would see if any of you DBA's out there have any advice.
>>> I'm building an inventory system that deals with inventory tracking,
>>> and also with where inventory is being sold (outlets).  I'm having a
>>> bit of a problem with dealing with outlet over rides.  For example, I
>>> have a pair of shoes and the shoes are available in different sizes.
>>> I'm selling these shoes on amazon.com and ebay.com.  I want my price
>>> to be different for amazon than with ebay.  I'm trying to build the
>>> tables that deal with the outlet differences.  Let me put up a
>>> simplified version of the current database:
>>>
>>> Product:
>>>  name
>>>  sku
>>>  price
>>>  manufacturer
>>>  weight
>>>  box-dimensions
>>>  type (can be master or normal.  If master then see options table)
>>>  ...etc...
>>>
>>> Product Option:
>>>  FK to Product
>>>  sku
>>>  desc
>>>  value
>>>
>>> Now I have the Outlet:
>>>
>>> Outlet:
>>>  name
>>>  url
>>>  ...etc...
>>>
>>> ProductOutlet:
>>>  FK to Product
>>>  FK to Outlet
>>>  price (overrides default price)
>>>  quantity
>>>
>>>
>>> Now here comes the part that I'm confused about.  How should I deal
>>> with the Product options?  I've thought of a few ways to deal with
>>> them, but none seem very good.  The ProductOutlet table is a many to
>>> many relationship.  It seems odd to me to add an option table that
>>> refers to a many to many lookup table.
>>>
>>> ProductOptionOutlet?
>>>  FK to ProductOutlet (navigating back to product gets tedious, I can
>>> add more FK's)
>>>  FK to ProductOption
>>>  quantity
>>>
>>> Does anyone have any suggestions for how I could deal with this?  I'm
>>> really not even certain if I should keep the way that I'm dealing with
>>> products and their options.
>>>
>>> Thanks,
>>> James Lance
>>>
>>> /*
>>> PLUG: http://plug.org, #utah on irc.freenode.net
>>> Unsubscribe: http://plug.org/mailman/options/plug
>>> Don't fear the penguin.
>>> */
>>>  ------------------------------------------------------------------------
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database:
>>> 270.6.21/1678 - Release Date: 9/18/2008 9:01 AM
>>>
>>>
>>>       
>> /*
>> PLUG: http://plug.org, #utah on irc.freenode.net
>> Unsubscribe: http://plug.org/mailman/options/plug
>> Don't fear the penguin.
>> */
>>
>>     
>
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */
>   
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.169 / Virus Database: 270.6.21/1678 - Release Date: 9/18/2008 9:01 AM
>
>   



More information about the PLUG mailing list