Designing related products table

Question

0.00/5 (No votes)

See more:

I am given a task to implement “related products” functionality in an existing database. Since I am a SQL Server novice and not sure how to complete this task I was wondering if there is a solution like adding another column to existing database - RelatedProductID. Our products table is like this:

ProductID int, PK
Name nvarchar(100)
Price decimal
CoverImageURL nvarchar(400)
-- there are couple more columns

Posted 17-Apr-13 22:27pm

DImitrijex

Add a Solution

Comments

Karthik Harve 18-Apr-13 4:35am

Your is not clear. are you asking how to add new column for the existing database table ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-04-17T22:34:00

The problem with that idea is that it does two things:
1) it restricts the number of "related products" you can have to a single item - if you look at somewhere like Amazon, that isn't often the case. If you buy a phone, you probably want a cover and a hands free kit for example. Conversely, a handsfree kit may apply to a dozen phones.
2) It makes it a pain to remove a product, because you have to check every other product to see if it is a related product of that as well.

There are two approaches I would consider: a separate table to hold a link between product and it's related item, or a relation group table where a group would hold say six related items. It depends how many items you are talking about, and how many relations you will be involving.

Lucas Valeriy · Answer 2 · 2013-04-17T22:32:00

Yes, there is a solution like you mentioned but it’ll only work if there is exactly one related product for each of your products.
However, if this is not the case you’ll need referencing table containing all relations between products.

You can try adding a new table:

CREATE TABLE dbo.ProductsRelation
(
    FirstProductId int not null,
    SecondProductId int not null,
    PRIMARY KEY (FirstProductId, SecondProductId)
)

In this way, you can retrieve all related products for some product with the query below:

SELECT SecondProductId
FROM ProductsRelation
WHERE FirstProductId = @ProductID

Designing related products table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0