3

I created an entity-relationship diagram (ERD) for a book publishing business context that I will detail bellow.

Entity types

The entity types of interest are:

  • Writer
  • Work (as a book)
  • Publisher
  • Split (some kind of contract, representing the share of the whole Work ownership between Writers and Publishers, totalling 100%)

Business rules

  1. A Writer can have multiple Publishers.
    • And a Publisher can have multiple Writers.
  2. A Work can have one or many Writers.
    • And a Writer can have many Works.
  3. Works can have one or many Publishers.
    • And a Publisher can have multiple Works.
  4. For each Writer or Publisher on a Work, a Split must be be assigned.
  5. A Split can have multiple Versions and each SplitVersion can have different WriterPercents and PublisherPercentage.

Example

Let us suposse that Splits Version No. 1 is as follows:

  • Writer 1 = 50%

  • Writer 2 = 50%

  • Publisher 1 = 50%

  • Publisher 2 = 50%

Then, one of the Writers modifies the Splits to Version no. 2:

  • Writer 1 = 55%

  • Writer 2 = 45%

  • Publisher 1 = 60%

  • Publisher 2 = 40%

Entity-Relationship diagram

Here is the ERD I have built so far:

Database mode

You can download the MySQL Workbench file that corresponds to the current schema, if necessary.

Note: This was my first model but I think some elements might not be relevant now, as per the following @MDCCL comment:

Can a specific Writer have a contract with a determined Publisher even if such publisher has issued none the Works of said writer?

And the answer is yes, did not thought of that. In this regard, should I introduce a new relationship into the diagram?

Questions

So, I would like opinions on:

  • Is my ERD representing the given rules correctly?

  • Is there some simpler layout that could solve the discussed scenario?

  • 1.Doesn't the rule #4 say PublisherSplit must reference PublisherWork, and WriterSplit must reference WriterWork? That is, no version of split of work is possible whithout taking part in work? – Serg May 15 '16 at 17:32
  • 1
    I think it is too simplistic. What about multi book contracts? More complex payment distributions? – TomTom May 15 '16 at 18:30
  • @Serg, correct, everyone in the split has part of the work. – Goce Ribeski May 16 '16 at 10:42
  • @TomTom, no multi book contracts. One book = one contract. – Goce Ribeski May 16 '16 at 13:38

Your Answer

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

Browse other questions tagged or ask your own question.