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

Question

Hi, I have a short question about database design. I also tried searching, but cannot find what I am looking for. So here is my question:

I have two database tables Idea and Media (1: N). Thus, basically, this means that one idea cannot have one, one or several media. BUT I asked myself if it is possible to define a table in which each idea should have at least one medium. If possible, how can I achieve this using MS SQL Server 2008?

I hope someone can help me.

thanks alot for your help

UPDATE: this is what it looks like now:

Answer 1

First, there is a design rule in which a table models one entity type or relationship between entity types, but not both. Therefore, I present three tables: Media (entity), Idea (entity) and IdeasMedia (relation). postscript you know that the only "media" is "average", right? :)

Here are a few standard SQL-92 DDLs that focus only on keys:

 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 ) ) ); 

Here's the chicken and egg scenario: it's impossible to create an idea without an IdeasMedia link, but can't create an IdeasMedia without creating an Idea !

An ideal (set-based) solution would be for SQL Standard to support multiple assignment, for example.

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

where a semicolon indicates the boundary of the SQL statement at which point constraints are checked, and commas representing subqueries.

Unfortunately, there are no plans to add this set-based paradigm to the SQL standard.

The SQL-92 solution (procedural) 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; 

Unfortunately, SQL Server does not support CREATE ASSERTION and CHECK constraints, which can reference other tables and deferred constraints!

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

• Create “helper” stored procedures for adding, modifying, and deleting Ideas and their corresponding IdeasMedia .
• Remove update privileges from tables to force users to use proxy.
• You can use triggers to process scripts when deleting Media and Idea .

Of course, this (again procedural) implementation is far from an ideal set-based approach, which probably explains why most SQL encoders turn a blind eye to the requirement of a 1: 1..N relationship and instead assume a constructor meant 1: 0. .N !!

Answer 2

In Ideas, you create FK (foreign key) in PK (primary key) in Media. At the same time, apply the NOT NULL to 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 am not saying that this is a great design, and I definitely don’t know what your tables store (this is shown by my poor example), but the idea cannot exist without a Media link for the link. There is no connection between you and back, you ask 1: N, not N: N, which you may need.

When you think about table names, your idea seems to be the opposite. I would have thought that you would have 1: Media for N: ideas, not 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 does not normalize, so you will need a third table to match the connections.