how to link different but related tables

Well, better is an example that I tried to keep simple.

Let's assume a customer/order database, with different type of products available.

• Client table -- customer(id_client, ...)

• Physical products table -- physical(id_sub_product, weight, width, height, ...)
• Non physical products table -- dematerialized(id_sub_product, file_size, file_link, ...)

Now, I would centralize all products:

• Product table -- product(id_product, id_sub_product, product_type, price, ...)

<< Note:
There is a 1-1 relationship between 'product' table and each sub-product table ('physical' and 'dematerialized'), I could merge those 3 tables into one with:
- as many fields as the sub-product which has the maximum number of fields.

• fields with generic names and different meanings depending on the type of the sub-product. That would give:
  • Product table -- product(id_product, product_type, field1, field2, field3, ...)

So, depending on the value in the 'product_type' field, the application would know the meaning of 'field1' (could be a weight if it is a physical product for example), idem 'field2' and so on. That mean that some of the 'fieldx' fields wouldn't have any meaning for some products and would not be used.
This doesn't feel right, is it? >>

Finally, to track orders, I need a table which hold order status:

• Order table -- order(id_order, id_client, date_of_order, ...)

When I want to withdraw orders belonging to a specific client, I would:
- inner join 'client' and 'order' tables (on 'id_client') to get a list
of orders ('id_order').
- inner join 'order' and 'order_list' (on 'id_order') to get a list of
products in those orders ('id_product').
- inner join 'order_list' and 'product' (on 'id_product') to get
specific (sub-product) info about purchased products ('id_sub_product' and 'product_type' (and also generic info like 'price' ...) ).

Now, that's the issue:
How would I get info from sub-product tables. The 'product_type' field ('product' table) could hold the name of the sub-product table (or an integer that references that table name). In this case, I would make subsequent requests (as many as the number of products I withdrew earlier(*) ) to get info about each product. That would mean (knowing the name of the sub-product table) finding sub-product info thanks to 'id_sub_product'.

(*) Actually, if there is 50 commands to look after which belongs to 10 different sub-product types, I would need 10 requests.

All this would work but it really doesn't feel right.
- I don't know if having the name of a table in another table is good
practice or not.
- I don't like having to make a first request (to get 'product_type' and
'id_sub_product') followed by so many requests (one for each product type).
- And each of the latter requests would need to be handled differently
(for example, I would render a web page differently depending on the product type) and that means that to perform specific sub-product actions, I would need to eval the 'product_type' field like in (PHP):

eval($product_type . "($product_info)"); That would call (for example):
dematerialized([100, '/data/music/life_on_mars.mp3']); (100 would mean 100Mo).

Or (without eval), putting all different actions in a big switch/case:

switch($product_type) {
case 'dematerialized':

	// Actions to build html code related to dematerialized product
	break;
case 'physical':
	// Idem for physical items
	break;

For this last part, anyhow I will need to code all different actions so this is not part of my question.

What I need is to store different kind of products and to withdraw the information with as few requests as possible, but more importantly, I'd like this database to be well designed and robust. I know a SQL request will never return rows with different number of fields but there has to be a better/simple way than what I described above. What about the above "Note", having only one product table with variable number and meaning of fields, I don't think this is "SQL right" but it's a shortcut ?

Thanks to anyone who read that far, this message is much longer than I initially thought.
Any help/comment appreciated!

--
mireero