Re: how to link different but related tables
From: mireero <mireero_at_free.fr>
Date: Tue, 15 Mar 2016 14:32:39 +0100
Message-ID: <56e80ef7$0$4562$426a34cc_at_news.free.fr>
>> ram_at_zedat.fu-berlin.de (Stefan Ram) writes:
>>> mireero <mireero_at_free.fr> writes:
>>>> physical(id_sub_product, weight, width, height, ...)
>>>> dematerialized(id_sub_product, file_size, file_link, ...)
>>>> product(id_product, id_sub_product, product_type, price, ...)
>>> SET foreign_key_checks = 0;
>>
>> SET foreign_key_checks = 0;
>> DROP TABLE IF EXISTS PHYSICAL;
>> DROP TABLE IF EXISTS DATA;
>> DROP TABLE IF EXISTS PRODUCT;
>> DROP VIEW IF EXISTS PHYSICAL_PRODUCT;
>> DROP VIEW IF EXISTS ALL_PRODUCT;
>> DROP VIEW IF EXISTS PHYSICAL_TYPED;
>> DROP VIEW IF EXISTS DATA_TYPED;
>> SET foreign_key_checks = 1;
>>
>> CREATE TABLE PRODUCT
>> ( PRODUCT SERIAL PRIMARY KEY,
>> PRICE DECIMAL ( 22, 2 ) );
>>
>> CREATE TABLE PHYSICAL
>> ( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
>> WEIGHT DECIMAL ( 12, 5 ),
>> FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
>> ON DELETE CASCADE );
>>
>> CREATE TABLE DATA
>> ( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
>> SIZE BIGINT,
>> FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
>> ON DELETE CASCADE );
>>
>> CREATE VIEW PHYSICAL_PRODUCT AS
>> SELECT * FROM PRODUCT NATURAL JOIN PHYSICAL;
>>
>> INSERT INTO PRODUCT ( PRICE ) VALUES ( 12.00 );
>>
>> INSERT INTO PHYSICAL ( PRODUCT, WEIGHT )
>> VALUES ( LAST_INSERT_ID(), 10.00 );
>>
>> SELECT * FROM PHYSICAL_PRODUCT;
>>
>> INSERT INTO PRODUCT ( PRICE ) VALUES ( 24.00 );
>>
>> INSERT INTO DATA ( PRODUCT, SIZE )
>> VALUES ( LAST_INSERT_ID(), 10.00 );
>>
>> CREATE TABLE TYPE_P AS SELECT 'P' AS TYPE;
>> CREATE TABLE TYPE_D AS SELECT 'D' AS TYPE;
>>
>> CREATE VIEW PHYSICAL_TYPED AS
>> SELECT PRODUCT, TYPE, WEIGHT FROM PHYSICAL
>> JOIN TYPE_P;
>>
>> CREATE VIEW DATA_TYPED AS
>> SELECT PRODUCT, TYPE, SIZE FROM DATA
>> JOIN TYPE_D;
>>
>> CREATE VIEW ALL_PRODUCT AS
>> SELECT PRODUCT.PRODUCT,
>> COALESCE( PHYSICAL_TYPED.TYPE, DATA_TYPED.TYPE ) AS TYPE,
>> PRICE, WEIGHT, SIZE FROM PRODUCT
>> LEFT JOIN PHYSICAL_TYPED ON PRODUCT.PRODUCT=PHYSICAL_TYPED.PRODUCT
>> LEFT JOIN DATA_TYPED ON PRODUCT.PRODUCT=DATA_TYPED.PRODUCT;
>>
>> SELECT * FROM ALL_PRODUCT;
>>
>> +---------+------+-------+----------+------+
>> | PRODUCT | TYPE | PRICE | WEIGHT | SIZE |
>> +---------+------+-------+----------+------+
>> | 1 | P | 12.00 | 10.00000 | NULL |
>> | 2 | D | 24.00 | NULL | 10 |
>> +---------+------+-------+----------+------+
>> 2 rows in set (0.00 sec)
>>
>>
>>
Date: Tue, 15 Mar 2016 14:32:39 +0100
Message-ID: <56e80ef7$0$4562$426a34cc_at_news.free.fr>
On 03/14/2016 01:58 PM, Jerry Stuckle wrote:
> On 3/13/2016 10:54 PM, Stefan Ram wrote:
>> ram_at_zedat.fu-berlin.de (Stefan Ram) writes:
>>> mireero <mireero_at_free.fr> writes:
>>>> physical(id_sub_product, weight, width, height, ...)
>>>> dematerialized(id_sub_product, file_size, file_link, ...)
>>>> product(id_product, id_sub_product, product_type, price, ...)
>>> SET foreign_key_checks = 0;
>>
>> SET foreign_key_checks = 0;
>> DROP TABLE IF EXISTS PHYSICAL;
>> DROP TABLE IF EXISTS DATA;
>> DROP TABLE IF EXISTS PRODUCT;
>> DROP VIEW IF EXISTS PHYSICAL_PRODUCT;
>> DROP VIEW IF EXISTS ALL_PRODUCT;
>> DROP VIEW IF EXISTS PHYSICAL_TYPED;
>> DROP VIEW IF EXISTS DATA_TYPED;
>> SET foreign_key_checks = 1;
>>
>> CREATE TABLE PRODUCT
>> ( PRODUCT SERIAL PRIMARY KEY,
>> PRICE DECIMAL ( 22, 2 ) );
>>
>> CREATE TABLE PHYSICAL
>> ( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
>> WEIGHT DECIMAL ( 12, 5 ),
>> FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
>> ON DELETE CASCADE );
>>
>> CREATE TABLE DATA
>> ( PRODUCT BIGINT UNSIGNED NOT NULL UNIQUE PRIMARY KEY,
>> SIZE BIGINT,
>> FOREIGN KEY ( PRODUCT ) REFERENCES PRODUCT ( PRODUCT )
>> ON DELETE CASCADE );
>>
>> CREATE VIEW PHYSICAL_PRODUCT AS
>> SELECT * FROM PRODUCT NATURAL JOIN PHYSICAL;
>>
>> INSERT INTO PRODUCT ( PRICE ) VALUES ( 12.00 );
>>
>> INSERT INTO PHYSICAL ( PRODUCT, WEIGHT )
>> VALUES ( LAST_INSERT_ID(), 10.00 );
>>
>> SELECT * FROM PHYSICAL_PRODUCT;
>>
>> INSERT INTO PRODUCT ( PRICE ) VALUES ( 24.00 );
>>
>> INSERT INTO DATA ( PRODUCT, SIZE )
>> VALUES ( LAST_INSERT_ID(), 10.00 );
>>
>> CREATE TABLE TYPE_P AS SELECT 'P' AS TYPE;
>> CREATE TABLE TYPE_D AS SELECT 'D' AS TYPE;
>>
>> CREATE VIEW PHYSICAL_TYPED AS
>> SELECT PRODUCT, TYPE, WEIGHT FROM PHYSICAL
>> JOIN TYPE_P;
>>
>> CREATE VIEW DATA_TYPED AS
>> SELECT PRODUCT, TYPE, SIZE FROM DATA
>> JOIN TYPE_D;
>>
>> CREATE VIEW ALL_PRODUCT AS
>> SELECT PRODUCT.PRODUCT,
>> COALESCE( PHYSICAL_TYPED.TYPE, DATA_TYPED.TYPE ) AS TYPE,
>> PRICE, WEIGHT, SIZE FROM PRODUCT
>> LEFT JOIN PHYSICAL_TYPED ON PRODUCT.PRODUCT=PHYSICAL_TYPED.PRODUCT
>> LEFT JOIN DATA_TYPED ON PRODUCT.PRODUCT=DATA_TYPED.PRODUCT;
>>
>> SELECT * FROM ALL_PRODUCT;
>>
>> +---------+------+-------+----------+------+
>> | PRODUCT | TYPE | PRICE | WEIGHT | SIZE |
>> +---------+------+-------+----------+------+
>> | 1 | P | 12.00 | 10.00000 | NULL |
>> | 2 | D | 24.00 | NULL | 10 |
>> +---------+------+-------+----------+------+
>> 2 rows in set (0.00 sec)
>>
>>
>>
> > This isn't even first normal form. Terrible design. >
Well that's quite interesting.
I didn't know about "coalesce", I will check that.
Actually, that means that if I have 50 different families of product,
I'll have 50 "left join", right ?
Ok for 50, but if I have thousands ?