Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    12

    table relation design question

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Posts
    12
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2007
    Posts
    12
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2007
    Posts
    12

    Thanks

    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.
    Last edited by kalmon; 08-23-07 at 14:54. Reason: Update

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, you are right to be confused, but that's unfortunately how mysql works

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2007
    Posts
    12
    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.
    Last edited by kalmon; 08-23-07 at 23:11.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no idea, sorry

    i don't pay any attention to upcoming releases

    got enough to do, helping people still on 3.23
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2007
    Posts
    12
    Thank you very much for the help. I've already setup the algorithm to change it on the application end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •