I'm new to mySQL and am trying to create a database for an add item page which will have inputs: name price and type. Type can be either 'Book', 'Audiobook' or 'Ebook' and depending on which type is selected, new input fields will be added to the page. If 'Book' is selected, inputs number-of-pages, weight and hardcover(boolean value) will be added. If 'Audiobook' is selected input length will be added. If 'Ebook' is selected input format will be added.
My question is what's the best table structure for this. Should this be done using one table or should I create four tables, one for generic item attributes and the other three for the attributes of specific item types like this:
//item table
id name price type
=============================
1 item1 22 Audiobook
2 item2 15 Book
//book table
id pageNum weight hardcover
=================================
1 340 22 yes
2 679 15 no
//audiobook table
id length
============
1 12:30h
2 6:00h
//ebook table
id format
============
1 pdf
2 epub
If so what would the query for joining the item table with the relevant table and inserting the data into the database look like?
