CODEWITHSUNDEEP
sqldatabasesql-server-2008
MUG4N
MUG4N

Reputation: 19717

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

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:

enter image description here

Upvotes: 7

Views: 8781

Answers (2)

onedaywhen
onedaywhen

Reputation: 57063

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

Upvotes: 4

vol7ron
vol7ron

Reputation: 42149

You create a FK (foreign key) in Idea to the PK (primary key) in Media. At the same time apply a NOT NULL constraint to the FK.

If you already have data in the table, see here

To illustrate:

Media               Idea
-----               ----
 id | type           id | description       | media_id
----+-----          ----+-------------------+----------
 1  | TV             90 |  advertise        | 2
 2  | Magazine       90 |  advertise        | 1
 3  | Mail           91 |  superbowl party  | 1
                     91 |  superbowl party  | 3

I'm not saying this is great design, and I definitely don't know what your tables are storing (indicated by my poor example), but the idea cannot exist w/o a Media entry to link to. There is no linking back and forth, you are asking for 1:N, not N:N, which you may want.

When thinking about the table names, it seems like your idea is backwards. I would think you would have 1:Media to N:Ideas instead of the other way around.

CREATE TABLE idea (
    id        integer 
  , media_id  integer NOT NULL REFERENCES media
)  

--or--

CREATE TABLE idea (
    id         integer
  , media_id   NOT NULL
  , FOREIGN KEY (media_id) REFERENCES media
);

Note: This is not normalized, so you would need a third table to match the joins.

Upvotes: 1

Related Questions