locked
Table Relationship RRS feed

  • 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


    • Edited by Corbex Saturday, September 22, 2012 6:17 AM
    Saturday, September 22, 2012 6:14 AM

Answers

  • 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.


    Monday, September 24, 2012 3:01 AM

All replies

  • 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

    Saturday, September 22, 2012 6:43 AM

  • 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

    Saturday, September 22, 2012 12:35 PM
  • 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?

    Sunday, September 23, 2012 3:26 AM
  • 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

    Sunday, September 23, 2012 3:33 AM
  • 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

    Sunday, September 23, 2012 7:26 AM
  • 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.


    Monday, September 24, 2012 3:01 AM
  • 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

    Monday, September 24, 2012 6:11 AM
  • 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


    • Edited by Corbex Tuesday, September 25, 2012 3:28 AM
    Tuesday, September 25, 2012 3:24 AM
  • 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

    Tuesday, September 25, 2012 3:31 AM
  • 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!



    • Edited by BIWORKMVP Tuesday, September 25, 2012 7:32 AM
    Tuesday, September 25, 2012 7:28 AM