Hello there I've got a short question about database design. I additionally attempted the search but aren't able to find things i am searching for. Here is my question:

I've two database tables Idea and Media (1:N). So essentially what this means is one idea might have none, one or more medias. However I requested myself if you can define the table that every idea should have a minumum of one media. If this sounds like possible how do i accomplish this with MS SQL Server 2008?

I really hope somebody can assist me.

Thx alot for the help

UPDATE: this is exactly what it appears like right now:

enter image description here

First, there's a design guideline that the table models whether single entity type or perhaps a relationship between entity types although not both. Therefore, I invisage three tables, Media (entity), Idea (entity) and IdeasMedia (relationship). p.s. you realize the singular of 'media' is 'medium', right? :)

Here's some Standard SQL-92 DDL that concentrates on secrets 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's a 'chicken and egg' scenario here: can't create a concept with with no referencing IdeasMedia but can't create an IdeasMedia without creating an Idea!

The perfect (set-based) solution could be for SQL Standard to aid multiple assignment e.g.

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

in which the semicolon signifies the SQL statement boundary after which constraints are checked and also the commas denoting the sub-claims.

Sadly, you will find no intends to add this set-based paradym towards the SQL Standard.

The SQL-92 (procedural) means to fix this really is the following:

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 does not support CREATE ASSERTION nor CHECK constraints that may make reference to other tables nor deferrable constraints!

Personally, I'd handle this in SQL Server the following:

  • Create 'helper' saved procs to include, amend and take away Ideas as well as their particular IdeasMedia associations.
  • Remove update rights in the tables to pressure customers to make use of the procs.
  • Possibly use triggers to deal with situations when removing Media and Idea organizations.

Certainly, this (again procedural) implementation is way taken off the perfect set-based approach, which most likely describes why most SQL programmers turn a blind eye to some requirement of single:1..N relationship and rather assume the designer meant 1:..N !!