0

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?

  • The PK for books should be book_id alone. If you want to make sure a book cannot be created without an author reference, declare author_id as not nullable (author_id integer not null). Declaring the FK as you did is enough to ensure that author_id is a valid reference. As for the actual question, I'm not sure I've understood what the trouble is. On the surface of it, it seems you could go about creating the content table the same way as you do about books: content_id as the PK and book_id as an FK referencing books. I'm probably missing something but not sure what that is. – Andriy M Jan 19 '18 at 8:35
  • FKs can't do everything. – Rick James Jan 20 '18 at 1:00
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.