Re: how to link different but related tables

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sun, 13 Mar 2016 22:11:00 -0400
Message-ID: <nc56dj$1n2$1_at_jstuckle.eternal-september.org>


On 3/13/2016 10:07 PM, Stefan Ram wrote:
> 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();
>

How does this solve his problem?

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================
Received on Mon Mar 14 2016 - 03:11:00 CET

Original text of this message