-1

I am working on a simple database design of an application. I have a Book Illustrator and Editor table.

Modelling 1 Relation between enter image description here

With this model, I think here is the duplication of the column name in each author editor and illustrator table.

What if a book author, illustrator and editor person are same, in this case, data get duplicated across 3 tables.

But in case of searching it will be faster, I guess as it no of items per table will be less.

Modelling 2

enter image description here With this modeling, all the author, illustrator and editor info get saved in a single table and I am confused what should be the name of this table.

With this approach. The data won't' get duplicated but the searching will be double as compared to model 1.

Can anyone suggest me which model should I choose. I feel modeling 2 is better.

shining
  • 1,049
  • 16
  • 31
  • It is a little bit unclear if your picture describes entities or the actual table columns. If you have e.g. "author_id" in the book tables, your relationship will not be N:N (as you can only have at most 1 author per book - which you should rethink if that is what you plan to do). You can get a N:N-relationship if you add an intermediate table with e.g. `(book_id, author_id)`. Your 2nd table is probably just a "person" table. You can then still use 3 N:N-tables (e.g. `authors(book_id, person_id)` or one table with an additional role, e.g. `(book_id, role_id, person_id)`. – Solarflare Sep 12 '17 at 07:59
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jan 08 '23 at 03:57

3 Answers3

1

It is purely up to your taste which model you should use. The second one has the advantage that you wont get duplicates. With both models you can get the results with one query

select * from books
left join names auth ON (auth.id = author_id) 
left join names ill ON (ill.id = illustrator_id) 
left join names ed ON (ed.id = editor_id) 
where books.id = 1;

SQLFiddle gives an example of model 2. If you want to obtain the data from model one, just change the 3 joins to the right table.

If you want to display a list of authors, I would not recommend adding it as a new field in the names table, but just use a joint query.

select auth.* from books
left join names auth ON (auth.id = author_id)

As long as you set the indexes on the id, author_id, illustrator_id and editor_id, you are fine.

Edit: my preference would go to model 2. I think it might also a bit faster:

  1. The database only needs to open one file (not 3)
  2. There are less records in the table (compared to the combined of the 3 tables) because you don't have duplicates.
  3. The database only need to search through one index set (not 3) and might do some optimised stuff in the back because it is looking for 3 keys in the same set (instead of 3 key in 3 index sets) - it's my gut feeling, not sure if this is exactly correct...
Jeffrey
  • 1,766
  • 2
  • 24
  • 44
0

You can make one amendment in the 2nd design you have proposed by keeping the user type column as well, which describes whether the user is any of author, illustrator and editor. the id will vary from 0 - 7, you can store the decimal value of the bitwise data. as if a person is Editor & Author then, 

1(Editor) 0(Illustrator) 1(Author) => 5

So when you will perform any select/search on that table you can add filters where user type in query.

Lucky Sharma
  • 173
  • 6
0

Do you need to validate, for example, that the author is defined as author in "Author" before you link to a book as author? Do you care to do a query to know who are all authors/editors/illustrators defined in your database?

You have created N-N link between the entities, however, you have the "auhorId", "editorId" and "illustatorId" in the "Book" entity!

The proper way would be to have the resolution of the many-to-many relationship by having another table, and end up with something like this

  1. BOOK, has ID, TITLE, DESC, etc.
  2. PARTICIPANT (suggested name for all people), has ID, NAME, BIO, etc
  3. AUTHOR, has BOOK_ID, PARTICIPANT_ID
  4. EDITOR, has BOOK_ID, PARTICIPANT_ID
  5. ILLUSTRATORS, has BOOK_ID, PARTICIPANT_ID
  6. OR, instead of (3, 4, 5), BOOK_PARTICIPANT, has BOOK_ID, PARTICIPANT_ID, PARTICIPATION_TYPE (code for author, editor, illustrator), or even use flags (IS_AUTHOR, IS_EDITOR, IS_PARTICIPANT, where one is required to be set)

If you need to validate the participant as author, editor, illustrator before being able to link to a book, you need to add three flags here to to PARTICIPANT: IS_AUTHOR, IS_EDITOR, IS_ILLUSTRATOR

Hope this helps

BA.
  • 924
  • 7
  • 10