locked
many to many with two tables : 1 data + 1 junction RRS feed

  • Question

  • Hi All

    I need to creat some database containing articles. not too exciting so far.

    It might be that an article is a 'group' consisiting of other articles so I try with an example

    001 : Fork
    002 : Knife
    003 : Spoon
    010 : Cutlery
               001 : Fork
               002 : Knife
               003 : Spoon
    020 : Cutlery Airplane
               001 : Fork
               003 : Spoon

    001, 002, 003, 010 and 020 are all articles. 010 and 020 are grouped articles consiting of the sub articels. Thus an article can have multiple sub articles (e.g. 010 has 001, 002 and 003), but a sub article can have multiple article groups (003 has 010 and 020).

    This should be done with a many-to-many relation if I am right (just shoot if I am wrong).

    I would like to keep articles in one table as they are all contain exact the same fields.

    Is it wise to do this with a junction table that refers to the same table on both sides ?

    tableArticles ----> junctionTable ----
             ^                                           |
             +-----------------------------------

    Friday, September 9, 2011 7:12 PM

Answers

  • Correct, You Need To Use A Table To Keep Relation Of Article and Sub Article. See Following

     

    Create Table Artis(Id int identity(1,1) Primary Key,ArtName VArchar(30))
    Go
    Insert into Artis VAlues('001 : Fork')
    Insert into Artis VAlues('002 : Knife')
    Insert into Artis VAlues('003 : Spoon')
    Insert into Artis VAlues('010 : Cutlery')
    Insert into Artis VAlues('020 : Cutlery Airplane')
    Go
    Create Table SubArtis(Id Int Identity(1,1),ParentId Int References Artis(Id),SubArtId int References Artis(Id))
    Go
    Insert into SubArtis Values(4,1),(4,2),(4,3),(5,1)
    Go
    Select A.ArtName,C.ArtName SubArt From Artis As A
    left JOin SubArtis B On A.Id=B.ParentId
    left join Artis C On B.SubArtId=C.Id
    Go
    
    


     

     

     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Edited by Kuldeep Bisht Saturday, September 10, 2011 4:09 AM
    • Marked as answer by bart___s Saturday, September 10, 2011 12:58 PM
    Saturday, September 10, 2011 4:09 AM

All replies

  • Correct, You Need To Use A Table To Keep Relation Of Article and Sub Article. See Following

     

    Create Table Artis(Id int identity(1,1) Primary Key,ArtName VArchar(30))
    Go
    Insert into Artis VAlues('001 : Fork')
    Insert into Artis VAlues('002 : Knife')
    Insert into Artis VAlues('003 : Spoon')
    Insert into Artis VAlues('010 : Cutlery')
    Insert into Artis VAlues('020 : Cutlery Airplane')
    Go
    Create Table SubArtis(Id Int Identity(1,1),ParentId Int References Artis(Id),SubArtId int References Artis(Id))
    Go
    Insert into SubArtis Values(4,1),(4,2),(4,3),(5,1)
    Go
    Select A.ArtName,C.ArtName SubArt From Artis As A
    left JOin SubArtis B On A.Id=B.ParentId
    left join Artis C On B.SubArtId=C.Id
    Go
    
    


     

     

     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Edited by Kuldeep Bisht Saturday, September 10, 2011 4:09 AM
    • Marked as answer by bart___s Saturday, September 10, 2011 12:58 PM
    Saturday, September 10, 2011 4:09 AM
  • Cool... with example how to do. Going to implement this
    Saturday, September 10, 2011 12:58 PM
  • If the groups are not articles themselves, just arbitrary groupings of articles:

    Article
    -------
    Name (PK)
    Description

    Group
    -------
    Name (PK)
    Description

    Group_Article
    ----------------
    Group(FK)(PK)
    Article(FK)(PK)

    If the groups are also articles, and the groups are simply "related articles":

    Article
    -------
    Name (PK)
    Description

    Article_Article
    -----------------
    Article(FK)(PK)
    Article_Group(FK)(PK)
    CONSTRAINT CHECK(Article <> Article_Group)
    Monday, September 12, 2011 12:15 PM
    Answerer