Re: how to link different but related tables

On 3/13/2016 5: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:
>

Why would there be a 1-1 relationship between the product table and a sub-product table? You don't have two products with the same size or color? Or, why are you trying to put unrelated attributes in one table (color is not related to size, for instance). Think of individual attributes, not specific combinations.

> -- 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? >>
>

No, would not be correct. You should rethink your design to properly handle unrelated attributes. You will want many more than three tables.

> 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.

See above.

> 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'.
>

Again, see above. You will need to join more tables, but the database will be properly normalized.

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

Properly designed and you can do it in one statement. Even with what you have, you could do it with one SQL statement. Look at LEFT JOIN, for instance.

> 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;
> ...
> }
>

Terrible. Code should never depend on database design.

> 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

As I said - normalize, normalize, normalize. Different attributes such as color and size are unrelated and should be handled that way. Properly JOINing the tables in your SQL statement will allow you to pick the appropriate values for any item in your table.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================