locked
E-Commerce Table question... Product Options... RRS feed

  • Question

  • Hello all this is my first foray into SQL !! 

    Basically  have a table  

    Products

    ProductId

    ProductTitle

    ProductPrice

    ProductOptions

    the I have 

    Options

    OptionId

    OptionTitle

    in the Option table I have

    OptionId 1 = Red

    OptionId 2 = Blue

    OptionId 3 = Green

    How would I tell the Product  to show only Option 1 and Option 3 for a product ?

    Thursday, February 9, 2012 3:07 PM

Answers

  • Hello,

    You need to remove ProductOptions from Product and create another table (Actually ProductOptions is the good name) to store the link between Products and Options. Basically something like that:

    create table dbo.ProductOptions
    (
    	ProductId int not null,
    	OptionId int not null,
    	
    	constraint PK_ProductOptions
    	primary key clustered(ProductId, OptionId),
    	
    	constraint FK_ProductOptions_Product
    	foreign key(ProductId)
    	references dbo.Products(ProductId)
    	on update cascade 
    	on delete cascade,
    	
    	constraint FK_ProductOptions_Options
    	foreign key(OptionId)
    	references dbo.Options(OptionId)
    	on update cascade 
    )
    go
    
    create unique nonclustered index IDX_ProductOptions_OptionId_ProductId
    on dbo.ProductOptions(OptionId, ProductId)
    go
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Thursday, February 9, 2012 3:44 PM

All replies

  • Hello,

    You need to remove ProductOptions from Product and create another table (Actually ProductOptions is the good name) to store the link between Products and Options. Basically something like that:

    create table dbo.ProductOptions
    (
    	ProductId int not null,
    	OptionId int not null,
    	
    	constraint PK_ProductOptions
    	primary key clustered(ProductId, OptionId),
    	
    	constraint FK_ProductOptions_Product
    	foreign key(ProductId)
    	references dbo.Products(ProductId)
    	on update cascade 
    	on delete cascade,
    	
    	constraint FK_ProductOptions_Options
    	foreign key(OptionId)
    	references dbo.Options(OptionId)
    	on update cascade 
    )
    go
    
    create unique nonclustered index IDX_ProductOptions_OptionId_ProductId
    on dbo.ProductOptions(OptionId, ProductId)
    go
    


    Thank you!

    My blog: http://aboutsqlserver.com

    Thursday, February 9, 2012 3:44 PM
  • Dmitri is correct. What you have is a many to many relationship. For example, A Product can have many Options and a Option can describe many Products. To normalize, create an intersection table as Dmitri stated. Make both columns in your intersection table primary keys so it has a clustered index (if applcable in your situation and all rows are unique) otherwise use a covering index. 

    John

    http://knowledgy.org

    Saturday, February 11, 2012 12:54 AM
  • I this that you mean how to select from the Product table only the ProductOption of 1 and 3

    if i am correct please follow this code

    Select *
    From Product
    Where ProductOptions = 1 Or ProductOptions = 3

    Thanks

    • Proposed as answer by MohamedSakr Monday, February 13, 2012 7:24 AM
    • Unproposed as answer by Brian TkatchEditor Monday, February 13, 2012 2:52 PM
    Sunday, February 12, 2012 9:06 AM
  • WHERE ProductOptions IN (1,3)

    Monday, February 13, 2012 2:52 PM
    Answerer