Re: how to link different but related tables
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Tue, 15 Mar 2016 11:03:45 -0400
Message-ID: <nc982f$f54$1_at_jstuckle.eternal-september.org>
>>> 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)
>>>
>>>
>>>
Date: Tue, 15 Mar 2016 11:03:45 -0400
Message-ID: <nc982f$f54$1_at_jstuckle.eternal-september.org>
On 3/15/2016 9:32 AM, mireero wrote: > On 03/14/2016 01:58 PM, Jerry Stuckle wrote: >> 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. >> > > Well that's quite interesting. > I didn't know about "coalesce", I will check that. > > Actually, that means that if I have 50 different families of product, > I'll have 50 "left join", right ? > Ok for 50, but if I have thousands ? > > _at_Jerry: > "terrible" is quite a strong word, what would you propose? > "Normalizing", I got that, but how exactly? > > Thanks guys
Yes, "terrible" is quite accurate. Read up on normalization - it's quite more than can be taught in a newsgroup posting. Wikipedia actually has some good information on it (rare, I know), as well as other sites.
And no, you don't have individual tables for "families of products". You do have independent attributes, however. For instance, color and weight are two independent attributes.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Tue Mar 15 2016 - 16:03:45 CET