Disclaimer that I'm not very versed in relational database design and normalization, but this is something that I've encountered and I'm not sure if this is a normalized design. Let's say there's a parent "company" table

companies

and there are Books and Authors that are created under a company

books

authors

Now if we want to assign books to authors or vice versa:

books_authors

but we'll also be permitted to connect books and authors that are not under the same company:

Is there a better design than this that would prevent books and authors of different companies from being associated with each other? Do I just have to add a check/constraint to prevent that, or is there some other way to tackle this?

CodePudding user response：

As you have it set up, the company owns the book and the author. This doesn't match reality; author(s) write books, companies publish books, and a book can have many editions published by many companies.

Formally:

• A book has many authors.
• A book has many editions.
• An edition has a company (the publisher).
• A book has many companies through their editions.

And from that we can derive...

• An author has many books.
• An author has many editions of their books.
• An author has many companies through their book's editions.
• A company has many editions.
• A company has many books through their editions.
• A company has many authors through their editions' books.

-- These can be publishers.
create table companies (
  id bigserial primary key,
  name text not null
);

-- These can be authors.
create table people (
  id bigserial primary key,
  name text not null
);

create table books (
  id bigserial primary key,
  title text not null
);

-- This can also include their role in the authoring.
-- For example: illustrator, editor
create table book_authors (
  book_id bigint not null references books,
  person_id bigint not null references people,

  -- Can't author the same book twice.
  unique(book_id, person_id)
);

-- This can also include information about the publishing such
-- as its year and language.
create table book_editions (
  book_id bigint not null references books,
  company_id bigint not null references companies,
  edition text not null,

  -- The same company can't publish the same edition twice.
  unique(book_id, company_id, edition)
);

This is a generic, flexible book/author/publisher schema. However, the schema should be primarily defined by how you intend to use it.