Table Relationship

Question

Hi everybody

There are 3 tables: Item, Category and SubCategory.
Each Category (A, B, C, etc) has many SubCategories (A1, A2..., B1, B2..., C1, C2..., etc), and the relationship is one-to-many.
(table SubCategory has CategoryID as FK).

An Item can belong to many Categories and a Category has many Items (many-to-many relationship).
An Item can belong to many SubCategories and a SubCategory has many Items (many-to-many relationship).

I.e. an Item can belong to Categories A and C and to Subcategories A2, A4, C1, C5.

Moreover, Category A can have Items X, Y, Z, and SubCategory A2 can have say Items X and Y.

How can the Item table be related to Category and SubCategory tables so that no replication of data occurs?

Thanks in advance, Corbex

Answer 1

I think you need to have a 'junction' table named Item_Category_SubCat or something that will contain three columns ItemID,CatID,SubCatID

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 2

An Item can belong to many Categories and a Category has many Items (many-to-many relationship).
An Item can belong to many SubCategories and a SubCategory has many Items (many-to-many relationship).

How about if you eliminate the first option?

If you do, you have proper hierarchical relationship: item --> subcategory --> category

For additional assistance, can you list some examples?

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 3

Hi Uri. Thanks for your response. As a matter of fact I did think about it but I'm not quite sure.

The procedure goes like this:

1. An Item is assigned one or more Categories, then,

2. The Item is assigned Subcategories based on Categories already assigned.

What do you think?

Answer 4

Hi Kalman! Thanks for your quick response. As I responded to Uri the procedure goes like this:

1. An Item is first assigned one or more Categories, then,

2. The Item is assigned Subcategories based on Categories already assigned.

As you can see it is necessary for Items to be assigned Categories before they can be assigned to Subcategories.

Did I make it more clear to you?

Corbex

Answer 5

It is ok,having that table allows this procedure as  you described above.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 6

Hi Corbex,

You can add a foreign key in Item table points to Category table, and then add a unique constraint on Item table so that one item can only point to one record in Category table once. I made a simple example, you can refer to it:

create table Item
(
     ID int not null,
     Name varchar(50),
     CategoryID int not null
)

Create table Category
(
     ID int primary key identity (1,1),
     Name varchar(50)
)

Create table SubCategory
(
     ID int primary key identity (1,1),
     Name varchar(50),
     CategoryID int not null
)
alter table Item
ADD CONSTRAINT FK_Item_Category FOREIGN KEY (CategoryID) references Category (ID);

alter table SubCategory
ADD CONSTRAINT FK_SubCategory_Category FOREIGN KEY (CategoryID) references Category (ID);

alter table Item
add constraint uc_Category unique (ID,CategoryID);

insert into Category values ('A');
insert into Category values ('B');
insert into Category values ('C');

insert into SubCategory values ('A1',1);
insert into SubCategory values ('A2',1);
insert into SubCategory values ('B1',2);
insert into SubCategory values ('B2',2);
insert into SubCategory values ('C1',3);
insert into SubCategory values ('C2',3);

insert into Item values (1,'item1',1);
insert into Item values (1,'item1',3);

-- the following record cannot be inserted
insert into Item values (1,'item1',1);

---

Best Regards,
Allen
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

As you can see it is necessary for Items to be assigned Categories before they can be assigned to Subcategories.

I don't get it.

Why don't you assign it just to subcategories? You can derive the category from the subcategory.

The business process does not make sense.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 8

Hi Kelman.

I understand your point. It's just a matter of procedure. When an Item is registered it must be assigned one or more Categories BEFORE getting assigned Subcategories. Assigning Subcategories according to already assigned Categories is another-following up step. I have thought right from the beginning the possibility only assigning Subcategories, and not Categories, to Items, as existing Subcategories are already assigned to respective Categories. I've applied such a tactic in other similar situations. It would be straightforward. Unfortunately this is not the case here. After all, this is exactly the reason I ask for a possible solution in order to avoid data duplication. I just try to avoid having two many-to-many relationships, the first between Item and Categories, and the second between Item and Subcategory and having to create junction tables to define those many-to-many relationships. It's a pain...

Corbex

Answer 9

Hi Corbex,

You can add a foreign key in Item table points to Category table, and then add a unique constraint on Item table so that one item can only point to one record in Category table once. I made a simple example, you can refer to it:

create table Item
(
     ID int not null,
     Name varchar(50),
     CategoryID int not null
)

Create table Category
(
     ID int primary key identity (1,1),
     Name varchar(50)
)

Create table SubCategory
(
     ID int primary key identity (1,1),
     Name varchar(50),
     CategoryID int not null
)
alter table Item
ADD CONSTRAINT FK_Item_Category FOREIGN KEY (CategoryID) references Category (ID);

alter table SubCategory
ADD CONSTRAINT FK_SubCategory_Category FOREIGN KEY (CategoryID) references Category (ID);

alter table Item
add constraint uc_Category unique (ID,CategoryID);

insert into Category values ('A');
insert into Category values ('B');
insert into Category values ('C');

insert into SubCategory values ('A1',1);
insert into SubCategory values ('A2',1);
insert into SubCategory values ('B1',2);
insert into SubCategory values ('B2',2);
insert into SubCategory values ('C1',3);
insert into SubCategory values ('C2',3);

insert into Item values (1,'item1',1);
insert into Item values (1,'item1',3);

-- the following record cannot be inserted
insert into Item values (1,'item1',1);

---

Best Regards,
Allen
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Thanks Allen. I'll check your solution and let you know.

Regards Corbex

Answer 10

Hi Corbex,

Correct me if I misunderstand your question, here's my solution.

IF OBJECT_ID('ItemsMapping','U') IS NOT NULL
DROP TABLE ItemsMapping
GO
IF OBJECT_ID('Item','U') IS NOT NULL
DROP TABLE Item
GO
IF OBJECT_ID('SubCategory','U') IS NOT NULL
DROP TABLE SubCategory
GO
IF OBJECT_ID('Category','U') IS NOT NULL
DROP TABLE Category
GO
CREATE TABLE Category
(
   ID INT PRIMARY KEY IDENTITY(1,1),
   Name VARCHAR(50)
)
INSERT INTO Category VALUES('C1'),('C2'),('C3')
CREATE TABLE SubCategory
(
   ID INT PRIMARY KEY IDENTITY(1,1),
   CategoryID INT FOREIGN KEY REFERENCES Category(ID),
   Name VARCHAR(50)
)
INSERT INTO SubCategory VALUES(1,'S1'),(1,'S2'),(2,'S3'),(3,'S4'),(3,'S5')
CREATE TABLE Item
(
    ID INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(50)
)
INSERT INTO Item VALUES('T1'),('T2'),('T3')
CREATE TABLE ItemsMapping
(
   ItemID INT FOREIGN KEY REFERENCES Item(ID),
   TypeID INT, -- CategoryID or SubCategoryID,
   MappingType CHAR(1) CHECK (MappingType IN ('C','S')) -- C(CategoryID), S(SubCategoryID)
) 
INSERT INTO ItemsMapping VALUES
(1,1,'C'),(1,2,'S'),(1,3,'C'),(2,1,'C'),(3,5,'S')
SELECT * FROM Category
-- 1	C1
-- 2	C2
-- 3	C3
SELECT * FROM SubCategory
-- 1	1	S1
-- 2	1	S2
-- 3	2	S3
-- 4	3	S4
-- 5	3	S5
SELECT * FROM Item
-- 1	T1
-- 2	T2
-- 3	T3
SELECT * FROM ItemsMapping 
-- 1	1	C
-- 1	2	S
-- 1	3	C
-- 2	1	C
-- 3	5	S 
-- Item:Category many to many
SELECT C.Name AS Category ,I.Name AS Item
FROM ItemsMapping AS IM 
INNER JOIN Item AS I 
ON IM.ItemID = I.ID
   AND IM.MappingType = 'C' -- Category 
INNER JOIN Category AS C
ON IM.TypeID = C.ID
-- C1	T1
-- C3	T1
-- C1	T2
-- Item:SubCategory many to many
SELECT S.Name AS SubCategory,I.Name AS ItemName 
FROM ItemsMapping AS IM 
INNER JOIN Item AS I 
ON IM.ItemID = I.ID
   AND IM.MappingType = 'S' -- SubCategory 
INNER JOIN SubCategory AS S
ON IM.TypeID = S.ID
-- S2	T1
-- S5	T3

Item T1 can belong to Category C1 and C3, it also can belong to SubCategory S2

---

Please vote if it's helpful and mark it as an answer!