Re: how to link different but related tables

On 03/13/2016 10:23 AM, mireero wrote:
> 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.
>
> -- Client table --
> customer(id_client, ...)
>
> 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:
>
> -- Order table --
> order(id_order, id_client, date_of_order, ...)
>
> 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'.
>
> (*) 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

Well, thanks everyone.
I got enough info to know how to head my head in the right direction. Received on Mon Mar 21 2016 - 12:54:12 CET