Re: how to link different but related tables
Date: 14 Mar 2016 02:54:19 GMT
Message-ID: <subtypes-20160314035354_at_ram.dialup.fu-berlin.de>
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) Received on Mon Mar 14 2016 - 03:54:19 CET