Mysql – How do we express a weak entity to be dependent on another weak entity

database-designerdMySQLmysql-workbench

Perhaps I am looking at my entities as a hierarchy rather than a set of relationships; for sake of an example I have three tables:

  • Authors
  • Books
  • Content

Where Authors writes Books and Books contain content.

The rules for these entities is as follows:

  • Content cannot stand alone without association to a Book
  • a Book cannot stand alone without association to an Author

See below the following partial implementation:

CREATE TABLE authors(
   author_id integer,
   author_name varchar,
   author_age int,

   PRIMARY KEY (author_id)
);

CREATE TABLE books(
   book_id integer,
   author_id integer,
   book_title varchar,
   book_published date,

   PRIMARY KEY (book_id, author_id),
   FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

From here I believe I have created a relationship where Books relies on Authors, should it decide to exist.

Arguably some may say that Content can simply just be part of Books, but say that Content has a hefty amount of columns in association to it and that a person would prefer to keep the more general Book data separate from its Content data for simplicity purposes.

In the event of the latter, how can I write a create statement that will make Content dependent on Books?

Best Answer

Using you syntax, I think the following might be what you're looking for:

CREATE TABLE authors(
   author_id integer,
   author_name varchar(100),
   author_age int,

   PRIMARY KEY (author_id)
);

CREATE TABLE books(
   book_id integer,
   book_title varchar(100),
   book_published date,
   author_id integer,

   PRIMARY KEY (book_id),
   FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE chapters(
   chapter_id integer,
   chapter_title varchar(100),
   book_id integer,
   content longblob,

   PRIMARY KEY (chapter_id),
   FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Each table has a unique id column as its primary key and a foreign key relationship to the table "above" it. These don't need to be part of the primary keys. For instance, book_id should be unique in and of itself, no need to include author_id as part of the primary key (assuming books only have one author). I added a table for chapters with basically the same relationship to books as books are to authors.

It might be helpful to think about the relationships a little differently. You say for instance, "a Book cannot stand alone without association to an Author" or "I have created a relationship where Books relies on Authors". It might be better to think of authors simply as a look-up [-Who wrote this book? -Author 37 did (details on file elsewhere).] and the foreign-key constraint as simply requiring that look-up to be a legitimate author (i.e. present in the authors table).

To illustrate the non-hierarchy further, imagine that your books table not only has a look-up to authors, but also to publishers and copyright holders and so on (a "star" structure) and that another table, called editions, has multiple rows associated with each book (using the books table as a foreign-key look-up) and each edition also has a look-up to the company that printed that edition and so on and so on.

Of course you would need a completely different structure in the first place if, say, books could have more than one author, or each chapter is a short story with it's own author or authors, etc.

I hope this helps a bit.

Related Question