Re: how to link different but related tables

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 14 Mar 2016 02:07:16 GMT
Message-ID: <subtables-20160314025141_at_ram.dialup.fu-berlin.de>


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;
DROP TABLE IF EXISTS PHYSICAL;
DROP TABLE IF EXISTS DATA;
DROP TABLE IF EXISTS PRODUCT;
DROP VIEW IF EXISTS PHYSICAL_PRODUCT;
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; DELETE FROM PRODUCT WHERE PRODUCT = LAST_INSERT_ID(); Received on Mon Mar 14 2016 - 03:07:16 CET

Original text of this message