Many-To-Many Relations in Rdbms Databases

Many-to-many relations in RDBMS databases

Keep track of a many-to-many relationship in a table specifically for that relationship (sometimes called a junction table). This table models the relationship as two one-to-many relationships pointing in opposite directions.

CREATE TABLE customer (
    customer_id VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    PRIMARY KEY (customer_id));

CREATE TABLE publication (
    issn VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    PRIMARY KEY (issn));

-- Many-to-many relationship for subscriptions.
CREATE TABLE subscription (
    customer_id VARCHAR NOT NULL,
        FOREIGN KEY customer_id REFERENCES customer (customer_id),
    issn VARCHAR NOT NULL,
        FOREIGN KEY issn REFERENCES publication (issn),
    begin TIMESTAMP NOT NULL,
    PRIMARY KEY (customer_id, issn));

You then use the junction table to join other tables through it via the foreign keys.

-- Which customers subscribe to publications named 'Your Garden Gnome'?
SELECT customer.*
FROM customer
    JOIN subscription
        ON subscription.customer_id = customer.customer_id
    JOIN publication
        ON subscription.issn = publication.issn
WHERE
    publication.name = 'Your Garden Gnome';

-- Which publications do customers named 'Fred Nurk' subscribe to?
SELECT publication.*
FROM publication
    JOIN subscription
        ON subscription.issn = publication.issn
    JOIN customer
        ON subscription.customer_id = customer.customer_id
WHERE
    customer.name = 'Fred Nurk';

How to represent a many-to-many relationship in a relational database?

One way is to create a third table with two columns, one contains CountryID the other RegionID where these are respectively the unique identifiers of Country and Region.

A row in this table means a relationship between a Country and a Region. As you can have more than one row in the table, you can store many-to-many relationships. If there is no relationship ( some countries are in no region ), there is no row in the table.

Here is an example

Table 1 - Country

ID Name

1 Spain

2 France

3 Germany

4 Norway

5 Belguim

Table 2 - Region

ID Name

1 Europe

2 BeneLux

3 EU Trading Region

4 ASIA

Table 3 - CountryRegion

Country Region

1 1

2 1

3 1

4 1

5 1

1 3

2 3

3 3

5 2

Which has expressed the following -

Spain is in Europe ( Country 1, Region 1 )

France is in Europe

Germany is in Europe

Norway is in Europe

Belgium is in Europe

Spain is in EU

France is in EU

Germany is in EU

Belguim is in BeneLux

No countries are in ASIA

Ths may not be geographically complete, or correct but I hope it shows the principle.

database modeling with multiple many-to-many relations

First of all: it is not good modeling techniques:

Each address can be the address of x students and y workers

There is no need to specify an Address to two or more Students or Workers. If they have same Address you can repeat the Address. Note that: How many Students and Workers have the same Address?

In this case: Redundancy is better that Complexity.

Secondly: Your first approach is wrong (ID | Address_ID | Worker_ID | Student_ID) and make a Nullification trap in database. Your second approach is better.

Thirdly: I offer third approach.

You have Worker and Student tables. So absolutely these tables have common attributes. So You can make another table named it: Person. Then put all common attributes in it. Then you can relate Person with Address (many-to-many or one-to-many)

In this case there is inheritance between Worker and Person (and Student and Person). To Mapping the inheritance to relational model, you can use one-to-one relationship between Worker and Person (and Student and Person). In these one-to-one relationships, it's better to transfer Person_ID to Worker (and Person_ID to Student).

Many-to-many relations in database design

This is not naive, this is the proper way of an ER model. Separating entities with relations, the classic design pattern. Worry not about query/join overhead, RDBMSs are optimized for this and can fly through those join queries.

You can also make the relation table have (part_id,machine_id) as a compound primary key. Better yet, create them as indexed organized tables and avoid any (negligible) overhead of the table data.

Can somebody give a practical example of a many to many relationship?

One-to-many & many-to-many relationships are not the property of the data rather the relationship itself. And yes you do need bridging/third table for many-to-many relationship in perfect normalized RDBMS world. Lets see each of it with real life example:

One-to-many relationship: When one row of table A can be linked to one or more rows of table B.


Real Life Example: A car manufacturer makes multiple models of the cars, but a same car model can't be manufactured by two manufacturers.

Data Model:

Many-to-many relationship: When one row of table A can be linked to one or more rows of table B, and vice-versa.


Real Life Example: A user can belong to multiple community, and a community can have multiple users.

Data Model:

Why no many-to-many relationships?

Think about a simple relationship like the one between Authors and Books. An author can write many books. A book could have many authors. Now, without a bridge table to resolve the many-to-many relationship, what would the alternative be? You'd have to add multiple Author_ID columns to the Books table, one for each author. But how many do you add? 2? 3? 10? However many you choose, you'll probably end up with a lot of sparse rows where many of the Author_ID values are NULL and there's a good chance that you'll run across a case where you need "just one more." So then you're either constantly modifying the schema to try to accommodate or you're imposing some artificial restriction ("no book can have more than 3 authors") to force things to fit.

Map many to many relationship in object relational database (Oracle)

This is how i solved the issue. As mentioned in the question a collection of each object has to be kept in both the objects when it comes to many to many mapping.

      /*-------Creating types-----------*/

     /*Incomplete type of BookCategory nested table- To keep a collection of categories which the book belongs*/
     CREATE TYPE BookCategory_tbltyp;
     /*Incomplete type of CategoryBook nested table - To keep a collection of books which the category contains*/
     CREATE TYPE CategoryBook_tbltyp;

     /*Creating Book type*/
     CREATE TYPE Book_objtyp AS OBJECT(
        Id VARCHAR2(6),
        Name VARCHAR2(30),
        BookCategoryList BookCategory_tbltyp
     );
     /*Creating Category type*/
     CREATE TYPE Category_objtyp AS OBJECT(
        Id VARCHAR2(6),
        Type VARCHAR2(30),
        CategoryBookList CategoryBook_tbltyp
     );

    /*Creating BookCategory object type*/
    CREATE TYPE BookCategory_objtyp AS OBJECT(
      CategoryId VARCHAR2(6),
      Category_ref REF Category_obj
    );

    /*Creating CategoryBook object type*/
    CREATE TYPE CategoryBook_objtyp AS OBJECT(
      BookId VARCHAR2(6),
      Book_ref REF Book_obj
    );

    /*Completing incomplete BookCategory and CategoryBook nested table types*/
    CREATE OR REPLACE TYPE BookCategory_tbltyp AS TABLE OF BookCategory_objtyp;

    CREATE OR REPLACE TYPE CategoryBook_tbltyp AS TABLE OF CategoryBook_objtyp;

    /*-------Creating tables-----------*/

     /*Creating book table with the nested collection table*/
     CREATE TABLE Book_objtab OF Book_objtyp(
        PRIMARY KEY (Id)
     )
     OBJECT IDENTIFIER IS PRIMARY KEY
     NESTED TABLE BookCategoryList STORE AS BookCategoryList_tab(
          PRIMARY KEY(NESTED_TABLE_ID, CategoryId)
        )
        ORGANIZATION INDEX COMPRESS)
     RETURN AS VALUE;

    /*Creating category table with the nested collection table*/
     CREATE TABLE Category_objtab OF Category_objtyp(
        PRIMARY KEY (Id)
     )
     OBJECT IDENTIFIER IS PRIMARY KEY
     NESTED TABLE CategoryBookList STORE AS CategoryBookList_tab(
          PRIMARY KEY(NESTED_TABLE_ID, BookId)
        )
        ORGANIZATION INDEX COMPRESS)
     RETURN AS VALUE;

    /*Foreign keys are not supported in nested tables. So the solution is to use SCOPE. 
According to oracle documentation SCOPE cannot be used in CREATE TABLE statement. 
So we have to use ALTER TABLE*/
    ALTER TABLE BookCategoryList_tab
        ADD (SCOPE FOR (Category_ref) IS Category_objtab);

    ALTER TABLE CategoryBookList_tab
        ADD (SCOPE FOR (Book_ref) IS Book_objtab);

The SCOPE constraint is different from the referential constraint in that the SCOPE constraint has no dependency on the referenced object.
Ex: row object in Category_objtab may be deleted, even if it is referenced in the Category_ref column of the nested table.

Refer Sample Application Using Object-Relational Features for more info.

many-to-many relationship in database design

There's nothing inherently wrong with having a many-to-many relationship, you'll just need to create a Junction Table (which is what it sounds like you're referring to with articlesTags) to facilitate that relationship.

---

Related Topics