Product Category assignment - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Product Category assignment

Author

Topic

PeteA
Starting Member

8 Posts

Posted - 2011-03-12 : 19:31:21

I'm in the process of creating a database for the general purpose of learning.

Now, what I have for Products at the moment (cut down) is the following.

pkProductId, sku, title, description, price, costprice, quantity

now my question is this.

If I were to assign multiple categories to the same product what would be the most efficient method of doing so.

category table
--------------
pkCategoryId, CategroryName, parentcategoryId, description, CategoryAssignmentId

Here's my current thought on how to do it.

Have a new table, call it category mapping.

Then have the following.

fkCategoryId, pkProductId,

So when I assign multiple categories to the same product they are all on different rows. The only thought behind this is if there are 10,000 different products and 300 different categories or sub categorys. Potentially I could be looking at reduced performance.

Any Ideas on a better structure?

He who said nothing was impossible, never tried slamming a revolving door.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-12 : 21:49:36

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

You have lot of conceptual problems/ What the Hell us a pkProductId? It is redundancy, violation of ISO-11179 and non-relational.

CREATE TABLE Products
(sku CHAR(10) NOT NULL PRIMARY KEY
CHECK(sku LIKE '..') ,
product_name VARCHAR (15) NOT NULL,
product_description VARCHAR (150) NOT NULL,
retail_price DECIMAL (8,2) NOT NULL
wholesale_price DECIMAL (8,2) NOT NULL
onhand_qty INTEGER NOT NULL);

>> If I were to assign multiple categories to the same product <<

CREATE TABLE Product_Categories
(product_cat CHAR(5) NIT NULL PRIMARY KEY,
sku CHAR(10) NOT NULL
REFERENCES Products(sku));

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

PeteA
Starting Member

8 Posts

Posted - 2011-03-14 : 09:32:59

Apologies, I'll post DLL in future, thanks for the standards they make for interesting and informative reading.

pkProductId is the primary key, since SKU can change. So I was going to use it as the primary key and then reference everything off that.

I'll read up more on those standards and get back if I have any more questions.

He who said nothing was impossible, never tried slamming a revolving door.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources