Re: how to link different but related tables
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Mon, 14 Mar 2016 08:58:32 -0400
Message-ID: <nc6cbl$evr$1_at_jstuckle.eternal-september.org>
>
> 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: Mon, 14 Mar 2016 08:58:32 -0400
Message-ID: <nc6cbl$evr$1_at_jstuckle.eternal-september.org>
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.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Mon Mar 14 2016 - 13:58:32 CET