SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Store multiple product variations for 1 master product		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 11/20/2006 :  07:22:44  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Dylan writes "Hey Everyone,

I'm in the process of building on online product ordering system. Front end is asp.net with an MS SQL database backend.

Background: Basically I have two tables. One that contains master products. A second one that contains product variations each with its own unique number. (Essentially different colours; sizes; prices).

In the real world, each master product has multiple variations. So in the master product table I have created a column where I can specify a variation number.

The problem is that I need to be able to specify multiple variation numbers for each record in the master product table and then run a query that pulls the product from the master product table and the associated variation details.

Problem: I really dont know how to approach this? I was wondering if I could store the values in the Master Product table and seperate them with a character something like "1; 2; 5; 6; 12" etc and then somehow write a query that would go and get records from the variation table based on what was listed in the VariationNumbers field of the master product? I'm pretty sure I cant store values like that but I dont know how else to go about it.

If anyone has any ideas, I would appreciate any feedback.

Cheers, Dylan"

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 11/20/2006 :  07:46:44  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
I think you are doing it wrong way.

Instead of storing variation numbers for each master in master table, you need to put master product number in each of its variation.

For eg.

Master Product table

ProductID   Name
--------------------------
1    Car
2    Shirt

Product Variation table

VariationID      ProductID      VariationScheme
------------------------------------------------------
1                1              Ford
2                1              Santro
3                2              Park Avenue
4                2              Raymonds

in this way, you wont have to store variation IDs in some arcane way in master table.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000