CODEWITHSUNDEEP
database-designumldata-modelingerd
Michael Albers
Michael Albers

Reputation: 123

ERD one table to many tables relationships

I'm creating an ERD and a certain parts give me headaches.

My issue: I have four entities: magazine, book, newspaper, and webpage. Each entity has it's own attributes.

I want to take notes on each of those four sources. But a note can only refer to one of them. E.g. a note on a book, can't be a note on a magazine, newspaper, or webpage. Also, it should be possible to make multiple notes on a magazine, book, newspaper, or webpage.

After a couple of days of trial and error and searching the web and old fashioned books, I still can't find a way how to model this.

I think I need to use a N-ary association for this, but I am not really sure. Can someone help me out? How should this be modeled?

On a side note: I'm using Enterprise Architect for modelling.

EDIT: While thinking things over and thinking over the answers and comments, I created this ERD:

ERD

This feels like what I need, but in this case I think it is still possible to have 1 note to be tied to a magazine AND a book (AND a newspaper AND a website). Those ANDs should be ORs.

Hopefully this clarifies things a bit more. And hopefully I have the multiplicities correct (been looking at this too long, I guess).

Upvotes: 2

Views: 3736

Answers (2)

qwerty_so
qwerty_so

Reputation: 36295

Hmm. Maybe you make things too complicated. If all notes belong to single entities only they are clearly just properties of that entity. Else there is no reason to make them an entity on their own.

If you really need a Notes entity you would model your requirements like this: enter image description here

Edit The 0..1 multiplicity was meant to be 0..n by the OP. The relations would not change in any else aspect, though.

Upvotes: 0

reaanb
reaanb

Reputation: 10066

One way is to create a supertype (let's call it publications) on which you can hang your notes. We can include a mutual exclusion restriction to prevent a publication from playing more than one role.

I realize you asked how to model it but instead of a diagram I'll give you SQL DDL. My modeling tool won't let me set up the foreign key for magazines correctly.

CREATE TABLE publications (
    publication_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    publication_type ENUM('M','B','N','W') NOT NULL,
    KEY id_type (publication_id, publication_type)
);

CREATE TABLE magazines (
    publication_id INT(11) NOT NULL PRIMARY KEY,
    publication_type ENUM('M') NOT NULL,
    FOREIGN KEY (publication_id, publication_type)
    REFERENCES publications (publication_id, publication_type)
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE notes (
    note_id INT(11) NOT NULL PRIMARY KEY,
    publication_id INT(11) NOT NULL,
    FOREIGN KEY (publication_id)
    REFERENCES publications (publication_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

The enum ('M', 'B', 'N', 'W') is meant to distinguish between magazines, books, newspapers and webpages. I didn't show the tables for the last three, they're similar to the schema for magazines.

Some notes about entity-relationship terminology. First, relationships are between entities (and stored in tables), not between tables as your heading states. Second, tables have columns, while attributes are one-to-one associations between entities and values and represented by pairs of columns in a table.

EDIT:

A similar technique, but easier to implement after a database has been populated, is to create a new entity type to act as a container for notes, and incorporate them into your original entities where required. For example:

CREATE TABLE notables (
    notable_id INT(11) AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE magazines (
    magazine_id INT(11) NOT NULL PRIMARY KEY,
    notable_id INT(11) NULL,
    FOREIGN KEY (notable_id)
    REFERENCES notables (notable_id)
    ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE notes (
    note_id INT(11) NOT NULL PRIMARY KEY,
    notable_id INT(11) NOT NULL,
    FOREIGN KEY (notable_id)
    REFERENCES notables (notable_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

In this case it's not as easy to enforce mutual exclusion, i.e. a notable_id could in theory be reused.

Upvotes: 1

Related Questions