January 20, 2012 at 3:53 pm
I need help with this small task that has become huge for me.
I do appreceiate your guidance in advance.
I have some data that I need to import into new tables in a SQL server database.
The data consist of categfories, produts and scores.
One product may belong to different categories and one category may have different products;
therefore, I created a linked table that will take care of many-to-many categories and products.
I think I got this issue solved as you can see in the below sudo-code.
However, I am encontering a problem with the scoring table.
How do I relate the scores to the product and the categories?
Every product has a score value per category and
every category has a score value per product.
Any help will be greatly appreciated.
--Category Table
CREATE TABLE Category
(
CategoryID int,
CategoryName varchar(50)
)
INSERT into Category VALUES (1,'Category1');
INSERT into Category VALUES (2,'Category2');
INSERT into Category VALUES (3,'Category3');
INSERT into Category VALUES (4,'Category4');
INSERT into Category VALUES (5,'Category5');
--Category and Product Link Table to support many to many
CREATE TABLE CatProdLink
(
CategoryID int,
ProductID int
)
--Product table
CREATE TABLE Product
(
ProductID int,
ProductName varchar(150)
)
INSERT INTO Product VALUES (1, 'ProductName1');
INSERT INTO Product VALUES (2, 'ProductName2');
INSERT INTO Product VALUES (3, 'ProductName3');
--Score table
CREATE TABLE Score
(
ScoreID,
ScoreValue
)
INSERT INTO Score VALUES (1, '1');
INSERT INTO Score VALUES (2, '2');
INSERT INTO Score VALUES (3, '3');
INSERT INTO Score VALUES (4, '4');
INSERT INTO Score VALUES (5, '5');
INSERT INTO Score VALUES (6, '6');
INSERT INTO Score VALUES (7, '7');
INSERT INTO Score VALUES (8, '8');
INSERT INTO Score VALUES (9, '9');
INSERT INTO Score VALUES (10, '10');
For excample:
ProductName1 has score values so lets say 3 (1 through 10)
but also ProductName1 has 5 categories and each category has a score (1 through 10)
January 20, 2012 at 4:39 pm
I'd personally use your many to many table to hold the scores, as well as use it as your linker table.
You're storing scores according to a composite key of CategoryID and ProductID. You've got a table built for just that, your many to many. Add in a column for 'Score' and assign it there. Pull it out as necessary.
Since your product doesn't actually carry a score independent of the category, this would make the most sense to me. If a product can also carry an independent score, I'd add a column to product calling it 'productScore' and probably name the one in the many to many 'CatProdScore'.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 21, 2012 at 8:15 am
Thanks so much for the help!!! That worked perfectly. 🙂
January 22, 2012 at 12:22 am
JohnDBA (1/21/2012)
Thanks so much for the help!!! That worked perfectly. 🙂
Excellent. Sometimes it's easy to end up overthinking these things. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply