how to link different but related tables
Date: Sun, 13 Mar 2016 10:23:47 +0100
Message-ID: <56e531a3$0$3331$426a74cc_at_news.free.fr>
Hi,
Well, better is an example that I tried to keep simple.
Let's assume a customer/order database, with different type of products available.
Now, here is the issue:
Products can be of many different types (more than 100).
In this example, I'll just use 2 different kind of products:
- "Physical" products (that have to be posted) This type of products (let's say cds, dvds, bluerays...) have specific properties, like size and weight (to calculate postal charges par example).
- The other type is "de-materialized" products (like downloadable mp3, vod...) This kind of products have properties like a link to the mp3/divx file, its size (in Mo) ...
Both types have common fields, like the price.
So, we could go creating 2 tables (as I said, there would be much more in reality).
- 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:
And one which list each order:
-- Order list table --
order_list(id_order, id_product)
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'.
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;
...
}
-- mireeroReceived on Sun Mar 13 2016 - 10:23:47 CET