Imagine a project: I want to build a service containing a comprehensive list of all the books issued in a country (or in the whole world). I have access to various books databases containing imperfect data that overlaps.

The task is to design a database that would hold all the initial data coming from the other databases, but that would also allow me to (reversibly) merge the duplicated records (either manually or via an algorithm).

The initial data would concern books and their authors and would be inserted in the raw_* tables (please see the image bellow). As the initial data coming from different sources overlaps in a significant proportion, it is important to be able to provide to the final users a list of books and authors without duplicates. Because the metadata concerning the same book might differ from one source to another and there is no obvious merging algorithm to implement at the moment, it is necessary to retain the flexibility to identify duplicated records and change the merging algorithm at a future date.

Having relatively little experience with database design, I came up with the structure seen in the image bellow. The tables raw_* would contain the data coming from the other databases. Next, either manually or using a script, the duplicated records would somehow be identified and merged, and the resulted data would be stored in tables books and authors. Provided that the merging of duplicated records was done correctly, the books and authors tables would hold only unique records. The drawback of this solution is that my database holds mainly duplicated data (for ex: books vs raw_books), but this is needed because the merging strategy might involve keeping attributes from different raw_books.

My question is: is this design a good one ? Are there better patterns or methods for dealing with this kind of problems ?

Thank you.

Proposed database schema

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.