So I am trying to design a database that will allow me to connect one product with multiple categories. This part I have figured. But what I am not able to resolve is the issue of holding different type of product details.
For example, the product could be a book (in which case i would need metadata that refers to that book like isbn, author etc) or it could be a business listing (which has different metadata) ..
How should I tackle that?
I understand this may not be the sort of answer you are looking for however unfortunately a relational database ( SQL ) is built upon the idea of a structured predefined schema. You are trying to store non structured schemaless data in a model that was not built for it. Yes you can fudge it so that you can technically store infinite amounts of meta data however this will soon cause lots of issues and quickly get out of hand. Just look at Wordpress and the amount of issues they have had with this approach and you can easily see why it is not a good idea.
Luckily this has been a long standing issue with relational databases which is why NoSQL schemaless databases that use a document approach were developed and have seen such a massive rise in popularity in the last decade. It's what all of the fortune 500 tech companies use to store ever changing user data as it allows for individual records to have as many or as little fields ( columns ) as they wish whilst remaining in the same collection ( table ).
Therefore I would suggest looking into NoSQL databases such as MongoDB and try to either convert over to them, or use them in conjunction with your relational database. Any types of data you know need to have the same amount of columns representing them should be stored in SQL and any types of data you know will differ between records should be stored in the NoSQL database.
