How to bond N database table with one master-table?

Question

Lets assume that I have N tables for N Bookstores. I have to keep data about books in separate tables for each bookstore, because each table has different scheme (number and types of columns is different), however there are same set of columns which is common for all Bookstores table;

Now I want to create one "MasterTable" with only few columns.

|   MasterTable   |
|id. | title| isbn|     
| 1  | abc  | 123 |


| MasterToBookstores |
|m_id | tb_id | p_id |
| 1   |   1   |  2   |
| 1   |   2   |  1   |


|       BookStore_Foo          |
|p_id| title| isbn| date | size|     
| 1  | xyz  | 456 | 1998 | 3KB |
| 2  | abc  | 123 | 2003 | 4KB |

|       BookStore_Bar                  |
|p_id| title| isbn| publisher | Format |     
| 1  | abc  | 123 |   H&K     |   PDF  |
| 2  | mnh  | 986 |   Amazon  |   MOBI |

My question, is it right to keep data in such way? What are best-practise about this and similar cases? Can I give particular Bookstore table an aliase with number, which will help me manage whole set of tables?

Is there a better way of doing such thing?

Answer 1

I think you are confusing the concepts of "store" and "book".

From you comments and the example data, it appears the problem is in having different sets of attributes for books, not stores. If so, you'll need a structure similar to this:

The symbol: denotes inheritance¹. The BOOK is the "base class" and BOOK1/BOOK2/BOOK3 are various "subclasses"². This is a common strategy when entities share a set of attributes or relationships³. For the fuller explanation of this concept, please search for "Subtype Relationships" in the ERwin Methods Guide.

Unfortunately, inheritance is not directly supported by current relational databases, so you'll need to transform this hierarchy into plain tables. There are generally 3 strategies for doing so, as described in these posts:

• Interpreting ER diagram
• Parent and Child tables - ensuring children are complete
• Supertype-subtype database design

NOTE: The structure above allows various book types to be mixed inside the same bookstore. Let me know if that's not desirable (i.e. you need exactly one type of books in any given bookstore)...

---

¹ Aka. category, subclassing, subtyping, generalization hierarchy etc.

² I.e. types of books, depending on which attributes they require.

³ In this case, books of all types are in the many-to-many relationship with stores.