table relation design question

kalmon · #1 (**permalink**) 08-22-07, 14:47

I have a MySQL5.1 database that I'm trying to put together. I have a question as to design the item table with relation to a item type table.

Basically i have a Item table that has:
ItemNumber integer Primary Key
ItemName varchar(30) NotNull
ProductType smallint
ManufacturerID smallint FK to Manufacturer table
MaintVendorID smallint FK to MaintVendor table
couple other descriptive fields but not important here...

Now the question is how do i structure the ProductType table? We already have an existing Category system. i.e. 631 could be 600 Computer Related + 30 monitors + 1 LCD, or 622 would be 600 computer related + 20 printers + 2 Laser, 641 would be 600 Computer Related + 40 PCs + 1 laptop.

Now I want to have referential integrity between the item table and the product type and would still like to stay in 3NF. I'm open to all suggestions

r937 · #2 (**permalink**) 08-22-07, 16:37

Quote:

Originally Posted by kalmon

Now the question is how do i structure the ProductType table?

same as always -- ProductType would the the PK, and maybe Name or Descr would be the name/description of the product type

kalmon · #3 (**permalink**) 08-23-07, 09:08

So I should just repeat the data "computer related" in up to 99 rows? doesn't that denormalize the database too? data in that description field would not depend on the "nothing but the Key", 3NF. the description would depend only on the ones digit.

hundreds position determines main type, tens position sub type, ones specific item type. Basically if 1 month from now management decides that the product types need to be changed so that 400 is now computer related I should be able to update one row and the change will cascade through RI.

r937 · #4 (**permalink**) 08-23-07, 09:19

Quote:

Originally Posted by kalmon

So I should just repeat the data "computer related" in up to 99 rows? doesn't that denormalize the database too? data in that description field would not depend on the "nothing but the Key", 3NF. the description would depend only on the ones digit.

i'm sorry, i don't understand what you're talking about here

kalmon · #5 (**permalink**) 08-23-07, 10:48

ok from a row standpoint:
642 Computer Related, PCs, Desktop
641 Computer Related, PCs, laptop
632 Computer Relateb, Monitors, CRT
631 Computer Related, monitors, LCD
623 Computer Related, Printers, Impact
622 Computer Related, printers, Laser
621 Computer Related, Printers, Inkjet
616 Computer Related, Components, CDrom
615 Computer Related, Components, Keyboard
614 Computer Related, Componints, Mouse PS2
613 Computer Related, Components, Mouse USB
612 Computer Related, Components, Floppy Drive
611 Computer Related, Components, Hardrive

That is just a SMALL sample of what would be there, You are saying make 1 table with 2 fields, ProdType smallint, and PTDesc Varchar(50) and then Add a FK in Item to ProdType?

I see 2 MAJOR flaws in that:
1. If for any reason the product main type changes say 600 changed to 400, there is no way to guarantee that all the records got changed that needed to.
2. there is repeated data in the records, this could lead to update and insert Anomalies. I purposely misspelled 3 lines up there simulating a $5/hr user thats entering data.

If that is the only way to do it I guess I will I just hoped there was another way that enforced RI in the Product types.

maybe 3 tables:
TypeTeir1
TT1id smallint
TT1Desc varchar(20) 'field for the 100s / Top tier description

TypeTeir2
TT2id smallint
TT2PatentID smallint 'ralating the parent table
TT2Desc varchar(20) 'field for the 10s / Sub category

TypeTeir3
TT3id smallint
TT3Patrent smallint 'relating the tables
TT3Desc varchar(20) 'field for the 1s / 3rd tier description

The problem is I can not figure out how to relate the fields correctly.

r937 · #6 (**permalink**) 08-23-07, 10:53

okay, you have a hierarchy of product types (for some reason i did not understand this before)

Code:

create table ProductTypes
( ProductType  smallint    not null 
, ProductName  varchar(99) not null
, ParentType   smallint    null 
, primary key ( ProductType )
, foreign key ( ParentType ) 
         references ProductTypes ( ProductType )
);

insert into ProductTypes values
 ( 600, 'Computer Related' , null )
,( 640, 'PCs'              , 600 )
,( 642, 'Desktop'          , 640 )
,( 641, 'laptop'           , 640 )
, ...

kalmon · #7 (**permalink**) 08-23-07, 12:53

Thats it
Thank you for the help. I'm banging my head right now for not thinking of that earlier. Why try to relate 3 tables when 1 will do

. What you put was what I was looking for.

--Edit--
The original create statement didn't work but after playing with it i got:

Code:

DROP TABLE IF EXISTS `producttypes`;
CREATE TABLE  `producttypes` (
  `ProductType` smallint(5) unsigned NOT NULL,
  `PTDesc` varchar(40) NOT NULL,
  `ParentType` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`ProductType`),
  KEY `FK_ParentType` (`ParentType`),
  CONSTRAINT `FK_ParentType` FOREIGN KEY (`ParentType`) REFERENCES `producttypes` (`ProductType`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

With this I can insert rows but I can not change any rows that have a "child row", basically Update is not Cascading its erroring out. ex.

Code:

mysql> select * from producttypes;
+-------------+------------------+------------+
| ProductType | PTDesc           | ParentType |
+-------------+------------------+------------+
|         600 | Computer Related |       NULL |
|         640 | PCs              |        600 |
|         641 | Desktops         |        640 |
|         642 | Laptop           |        640 |
+-------------+------------------+------------+
4 rows in set (0.00 sec)
4 rows in set (0.00 sec)

UPDATE producttypes SET producttype = 652 WHERE producttype = 642; OK
UPDATE producttypes SET producttype = 651 WHERE producttype = 641; OK
UPDATE producttypes SET producttype = 650 WHERE producttype = 640; ERROR

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`item/producttypes`, CONSTRAINT `FK_ParentType` FOREIGN KEY (`ParentType`) REFERENCES `producttypes` (`ProductType`) ON DELETE NO ACTION ON UPDATE CASCADE)

shouldn't ON UPDATE fire and cascade the change to the 2 child rows?
I can workaround and Set the Parent field to NULL then change the row then reset the parent field to the new number. I just wanted to have the database Force that change on update.

r937 · #8 (**permalink**) 08-23-07, 18:50

yup, you are right to be confused, but that's unfortunately how mysql works

Quote:

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE.

-- http://dev.mysql.com/doc/refman/5.0/...nstraints.html

kalmon · #9 (**permalink**) 08-23-07, 21:35

Ah, oh well... Is MySQL the only one that does this? Is that going to be changed or is changed in 6.0 seeing as they are going away from InnoDB as the storage engine and using Falcon? I know that at the moment Falcon does not support FK at all.