1:N relationship where N must be at least one entry

Question

Hi there I have a short question about database design. I also tried the search but can't find what I am looking for. So here is my question:

I have two database tables Idea and Media (1:N). So basically this means one idea can have none, one or several medias. BUT I asked myself if it's possible to define the table that each idea must have at least one media. If this is possible how can I achieve this with MS SQL Server 2008?

I hope somebody can help me out.

Thx alot for your help

UPDATE: this is what it looks like at the moment:

Answer 1

First, there is a design rule of thumb that a table models either a single entity type or a relationship between entity types but not both. Therefore, I envision three tables, Media (entity), Idea (entity) and IdeasMedia (relationship). p.s. you know the singular of 'media' is 'medium', right? :)

Here's some Standard SQL-92 DDL that focuses on keys only:

CREATE TABLE Media (MediaID INTEGER NOT NULL UNIQUE);
CREATE TABLE Idea (IdeaID INTEGER NOT NULL UNIQUE);
CREATE TABLE IdeasMedia 
(
 MediaID INTEGER NOT NULL REFERENCES Media (MediaID), 
 IdeaID INTEGER NOT NULL REFERENCES Idea (IdeaID)
);
CREATE ASSERTION Idea_must_have_media DEFERRABLE 
   CHECK (
          NOT EXISTS (
                      SELECT * 
                        FROM Idea AS i 
                       WHERE NOT EXISTS (
                                         SELECT * 
                                           FROM IdeasMedia AS im 
                                          WHERE im.MediaID = i.IdeaID
                                        )
                     )
         );

There is a 'chicken and egg' scenario here: can't create an idea with without a referencing IdeasMedia but can't create an IdeasMedia without creating an Idea!

The ideal (set-based) solution would be for SQL Standard to support multiple assignment e.g.

INSERT INTO Media (MediaID) VALUES (22), 
   INSERT INTO Idea (IdeaID) VALUES (55), 
   INSERT INTO IdeasMedia (MediaID, IdeaID) VALUES (22, 55);

where the semicolon indicates the SQL statement boundary at which point constraints are checked and the commas denoting the sub-statements.

Sadly, there are no plans to add this set-based paradigm to the SQL Standard.

The SQL-92 (procedural) solution to this is as follows:

BEGIN TRANSACTION;
INSERT INTO Media (MediaID) VALUES (22);
SET CONSTRAINTS Idea_must_have_media DEFERRED;
-- omit the above if the constraint was declared as INITIALLY DEFERRED.
INSERT INTO Idea (IdeaID) VALUES (55);
INSERT INTO IdeasMedia (MediaID, IdeaID) VALUES (55, 22);
SET CONSTRAINTS Idea_must_have_media IMMEDIATE;
-- above may be omitted: constraints are checked at commit anyhow.
COMMIT TRANSACTION;

Sadly, SQL Server doesn't support CREATE ASSERTION nor CHECK constraints that can refer to other tables nor deferrable constraints!

Personally, I would handle this in SQL Server as follows:

• Create 'helper' stored procs to add, amend and remove Ideas and their respective IdeasMedia relationships.
• Remove update privileges from the tables to force users to use the procs.
• Possibly use triggers to handle scenarios when deleting Media and Idea entities.

Certainly, this (again procedural) implementation is far removed from the ideal set-based approach, which probably explains why most SQL coders turn a blind eye to a requirement for a 1:1..N relationship and instead assume the designer meant 1:0..N !!