How to Represent a Many-To-Many Relationship in a Relational Database

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.

What is the best way to represent a constrained many-to-many relationship within a relational database?

If you're not worried about history - ie only worried about "now", do this:

create table parking (
  car_id references car,
  space_id references space,
  unique car_id,
  unique space_id
);

By making both car and space references unique, you restrict each side to a maximum of one link - ie a car can be parked in at most one space, and a space can has at most one car parked in it.

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.

what's the best way to represent a many-to-many relationship in a database?

many <-> many relationships are usually modeled with an intermediate table. In your case you would have these tables:

User        UserPage       Page
------      -----------    -------
UserID      UserID         PageID
...         PageID         ...

The intermediate table (UserPage here) is where you store information that's particular to that set of ID's (your permissions, in this case).

how to represent symmetric many to many relationship

Well, you could certainly just assume that all friendships are symmetric and store that friendship only once, but that would mean that when you want to query for all of Taher's friends, you have to look for his ID in either column.

Alternately you could have a separate table of relationship ID's, and then a one-to-many table of relationship to user. This has the advantage that it would allow multi-person relationships if someday you want that, and would let you add meta-data about the relationship (when it started, who suggested it, whatever).

   User                
Id    Name          
1     Taher         
2     Deepak        

Relationship              
Id     StartDate           
1      2010-08-23

UserRelationship
RelationshipId UserId
1                1
1                2

On the other hand, on Facebook, for example, I can "friend" someone and they can decide not to friend me back. If you don't have the "redundant" approach that you're using now, how will you represent that not-yet-reciprocal friendship?

How to design a many to many relationship in the presence of inheritance

Get rid of the AP entity and rename Parent with Adult which will contain parents + authorized persons.
Your design could look like this:

child (1, n) (0, n) adult

[adult] parent (0, n) (1, n) [adult] authorized person

A child has at least 1 parent. An adult can have 0 child (authorized person) or many (parent).
A parent (adult entity) can authorized 0 or more AP and an AP (adult entity too) is authorized by at least 1 parent.

Then for pickups you only need relationships with the adult entity and the child entity.

Note that this design will allow a parent to be the AP for other children.

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.

How do I make a many to many relationship in database?

Create a DriverCar table which contains both DriverID and CarID and foreign keys to the appropriate tables.

Depending on your data access model you can either use a clustered PK or create a separate identity column.

Any other data (such as how long each driver has spent in each car) would be logged against this table.

---

Related Topics