CODEWITHSUNDEEP
sqlsqliteandroid-room
rot8
rot8

Reputation: 335

How to combine two databases?

I am asking myself how to best define a new database given two databases.

I have the following two databases

  1. Author where I save information about authors i.e. Name, Birthyear, Country, Genre etc.
  2. Book where I save information about the book i.e. AuthorName, ReleaseYear, Pages etc

How do I create a new database with the full information about all books and the author? I.e. a Book_Complete Database which includes Author.Name, ReleaseYear,Pages, Genre,ReleaseYear and Author.Birthyear, Author.Country?

Upvotes: 0

Views: 506

Answers (3)

pawmasz
pawmasz

Reputation: 103

If you have two tables in a database you can combine them using JOIN. Here is SQLite tutorial on how to use JOIN.

https://www.sqlitetutorial.net/sqlite-join/

On the information you provided I assume you can you columns Name in table Author, and AuthorName in table Book. You can try something like this

SELECT
  A.Name,
  B.ReleaseYear,
  B.Pages,
  B.Genre,
  B.ReleaseYear,
  A.Birthyear,
  A.Country
FROM
  Author A
  LEFT JOIN Book B
    ON A.Name = B.AuthorName

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95090

Basically, I have these create these two tables on my app and want to store the combined version on a server. Or would you suggest another approach?

Yes we would. You should have one table per entity, i.e. one for the authors and one for the books. Thus you don't store data redundantly (e.g. the author's name or the book's title), which could otherwise lead to inconsistencies later. You can read up on database normalization here: https://en.wikipedia.org/wiki/Database_normalization or google it.

Now you must decide how to link the tables. One author can write multiple books. Can one book be written by multiple authors? Give both tables IDs you can work with (or find some natural unique key, like the ISBN for the book and maybe name + birth year for the author - the name only will probably not suffice and maybe it won't even when combined with the year).

If one book can only be written by one author, you have a 1:n relation (one book written by one author, one author writing multiple books).

  • author (author_id, name, birth_year, country, ...)
  • book (book_id, title, author_id, release_year, pages, ...)

If one book can have several authors, you have an m:n relation (one book written by multiple authors, one author writing multiple books).

  • author (author_id, name, birth_year, country, ...)
  • book (book_id, title, release_year, pages, ...)
  • book_author (book_id, author_id)

You can always join the tables, so as to get a combined result.

Upvotes: 0

Prince Khanna
Prince Khanna

Reputation: 66

Better to go for a single database and having 2 tables in it like

Author Table
AuthorId (PK)
Name
Birthyear
Country
Genre

Book Table
BookId (PK)
AuthorId (FK)
ReleaseYear
Pages

Upvotes: 1

Related Questions