Many to Many Relation Design - Intersection Table Design

Many to Many Relation Design - Intersection Table Design

The second version is the best for me, it avoids the creation of an extra index.

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

Three Entity Intersection Table many-many-many

Yes.

Ternary relationships are quite widely used, although not as widely used as binary relationships. This can even be extended to quaternary or in general n-ary relationships.

One place where you can see n-ary relationships in database design is star schema. The fact table at the center of a star is an n-ary relationship, where n is the number of dimension tables referenced by the fact table.

The normalization process consists of detecting departures from some normal form, and decomposing tables in order to yield a more normalized equivalent. Decomposition sometimes results lowering the order of n-ary relationships.

Star schema deliberately goes the other way. That is why star schema and normalization are so frequently seen as being at odds with each other.

SQL: many to many resolution and intersection entities

Lets break down the problem a little bit.

You've got songs.

CREATE TABLE Songs
(
  SongID int,
  SongName varchar(50)
);

You've got playlist information. (Name of the playlist, who created it, blahwhatever).

CREATE TABLE PlaylistDef
(
  PlaylistID int,
  PlaylistName varchar(50)  
);

You've got songs who are in playlists. Songs can be in multiple playlists.

CREATE TABLE Playlist
(
  PlaylistID int,
  SongID int
);

Lets populate the tables:

INSERT INTO Songs
(SongID, SongName)
VALUES
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E');

INSERT INTO PlaylistDef
(PlaylistID, PlaylistName)
VALUES
(1, 'a'),
(2, 'b');

INSERT INTO Playlist
(PlaylistID, SongID)
VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(2, 2),
(2, 3),
(2, 4),
(2, 5);

Then you can now run something like this to see a specific playlist.

SELECT * FROM Playlist WITH (NOLOCK) WHERE PlaylistID = 2

And it'll return this:

PLAYLISTID  SONGID
2           2
2           3
2           4
2           5

Does this help?

How can I reference an intersection table of a many-to-many relationship?

It is more flexible if you save the doctor_id as well as the location_id along with the prescriptions data. Otherwise, you will run into difficulties if a doctor moves to another office after already having given some prescriptions, unless you make a new entry for every time period a doctor was present in a certain office. Furthermore, you won't be able to properly depict the situation when, exceptionally, another doctor is present in the office and makes a prescription despite he or she has no office assignment.

People tend to become ingenious if the system prevents them from inserting data for a technical reason, you should always consider such exceptions, because they will occur.

Many-to-many relationship without intersection table?

The second approach (with the project_cost table containing two foreign keys) is the correct way to model a many-to-many relationship.

But your idea with the shared forecast_id (with or without forecast table) exhibits that you are not thinking of a many-to-many relationship in the ordinary sense: if one project is associated with a certain set of costs, all other projects must either be associated with the same or a disjoint set of costs.

If that is what you want, I see no problem with removing the forecast table. There is no referential integrity you are losing that way.

If you have additional requirements, for example that there has to be at least a cost and a project for each existing forecast_id, things may change. That could be guaranteed with foreign keys from the forecast table, but not without that table.

database design pattern: many to many relationship across tables?

Here's how I would design it:

CREATE TABLE Pairables (
  PairableID INT IDENTITY PRIMARY KEY,
  ...other columns common to both Section and Content...
);

CREATE TABLE Sections (
  SectionID INT PRIMARY KEY,
  ...other columns specific to sections...
  FOREIGN KEY (SectionID) REFERENCES Pairables(PairableID)
);

CREATE TABLE Contents (
  ContentID INT PRIMARY KEY,
  ...other columns specific to contents...
  FOREIGN KEY (ContentID) REFERENCES Pairables(PairableID)
);

CREATE TABLE Pairs (
  PairID     INT NOT NULL,
  PairableId INT NOT NULL,
  IsSource   BIT NOT NULL,
  PRIMARY KEY (PairID, PairableID),
  FOREIGN KEY (PairableID) REFERENCES Pairables(PairableID)
);

You would insert two rows in Pairs for each pair.

Now it's easy to search for either type of pairable entity, you can search for either source or target in the same column, and you still only need one many-to-many intersection table.

one-to-many vs many-to-many association design and benefits

The two options are NOT identical; the latter is the correct choice to use in a many-to-many design. Using a joining table means that you can have many pages (A, B, C) and many menus (1,2,3) and you can associate a set of menus with a set of pages (yielding A1, A2, A3, B1, B2, B3, C1, C2, C3).

The first design assumes that any given menu can only have 1 page associated with it. Menu 1 can only be associated with one page (will it be A, B, or C?). To associate the same menu with multiple pages, you'll need to have one row in your menu table for each associated page.

---

Related Topics