Store multiple product variations for 1 master product

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Store multiple product variations for 1 master product

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-20 : 07:22:44

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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-20 : 07:46:44

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources