Re: how to link different but related tables

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
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

Original text of this message