Search code examples
databaseentity-relationship

Database - is it better to change this db architecture or leave it?

I have three tables in my database: Book, Series, Author.

These all have many-to-many relationships between each other.

Book - Series: many-to-many
Book - Author: many-to-many
Author - Series: many-to-many

So, this means I'll have to add three more tables in my database:

1. tableBookSeries, which will have id, idBook, idSeries
2. tableBookAuthor, which will have id, idBook, idAuthor
3. tableAuthorSeries, which will have id, idAuthor, idSeries

This means I'll have 6 tables in my database.

Is there a way for optimization of this database or is it good now? Can I shorten the number of tables, and will it do me any good?

Solution

  • In my Opinion there is no direct relation in Author - Series. An author writes Books only, which happen to be part or not of a Serie.

    In a DB would be something like this (Using 5 tables instead of 6):

    1. Book - Series: many-to-many - Correct
    2. Book - Author: many-to-many - Correct
    3. Author - Series: many-to-many - Incorrect (don't do this)

    Extra tables:

    1. tableBookSeries, which will have id, idBook, idSeries - Correct
    2. tableBookAuthor, which will have id, idBook, idAuthor - Correct
    3. tableAuthorSeries, which will have id, idAuthor, idSeries - Incorrect (don't do this)

    Avoid connection between Author and Series. If you need to know which series are related to an Author, you can still know by searching for his books. Likewise, to know who wrote a Serie, you can get the list of Authors using the associated Books.

    Hope i was helpful