Re: Database design suggestion needed

[Zawisza Czarny]

Hi!

Maybe I will try to say something about my design but with more care :)

CREATE TABLE IF NOT EXISTS `items` (
  `ID` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL COMMENT 'Item name (not unique)\n',
  `symbol` VARCHAR(255) NOT NULL COMMENT 'Item symbol (unique)\n',
  `groupID` SMALLINT UNSIGNED NOT NULL,
  `packages_ID` MEDIUMINT UNSIGNED NOT NULL,
  `description` VARCHAR(10240) NULL,
  `lastUpdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  CONSTRAINT `groupID`
    FOREIGN KEY (`groupID`)
    REFERENCES `groups` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_items_cases1`
    FOREIGN KEY (`cases_ID`)
    REFERENCES `cases` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE UNIQUE INDEX `symbol_UNIQUE` ON `items` (`symbol` ASC);
CREATE INDEX `groupID_idx` ON `items` (`groupID` ASC);
CREATE INDEX `fk_items_packages1_idx` ON `items` (`packages_ID` ASC);

ITEMS (elements) table must store information about every item in database. Suppose that those items are various items like resistors, capacitors, µC but also soldering tip. Every item has its package (smd 1206 or something else, in other words it's a “external look”). Item is assigned to a GROUP, the task of group is not only to split elements from each other, but (what is the most important thing) store information about what kind of parameters should describe the item. Example: “passive elements” group should contain “max voltage and resistivity” , “microcontrolers” “spi interfaces, max voltage, number of IO pins”

CREATE TABLE IF NOT EXISTS `groups` (
  `ID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_ID` SMALLINT UNSIGNED NOT NULL,
  `name` VARCHAR(128) NULL,
  `lastUpdate` TIMESTAMP NOT NULL,
  `allowSetsInGroup` TINYINT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_groups_groups1`
    FOREIGN KEY (`parent_ID`)
    REFERENCES `groups` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX `fk_groups_groups1_idx` ON `groups` (`parent_ID` ASC);

CREATE TABLE IF NOT EXISTS `groupParametersLinks` (
  `parameters_ID` SMALLINT UNSIGNED NOT NULL,
  `groups_ID` SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (`groups_ID`, `parameters_ID`),
  CONSTRAINT `fk_groupParametersLinks_parameters1`
    FOREIGN KEY (`parameters_ID`)
    REFERENCES `parameters` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_groupParametersLinks_groups1`
    FOREIGN KEY (`groups_ID`)
    REFERENCES `groups` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE INDEX `fk_groupParametersLinks_parameters1_idx` ON `groupParametersLinks` (`parameters_ID` ASC);
CREATE INDEX `fk_groupParametersLinks_groups1_idx` ON `groupParametersLinks` (`groups_ID` ASC);

CREATE TABLE IF NOT EXISTS `parameters` (
  `ID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  `unit` VARCHAR(128) NULL,
  `singleUnit` VARCHAR(128) NULL,
  `typeID` TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`, `typeID`),
  CONSTRAINT `fk_parameters_parametersTypes1`
    FOREIGN KEY (`typeID`)
    REFERENCES `parametersTypes` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE UNIQUE INDEX `ID_UNIQUE` ON `parameters` (`ID` ASC);
CREATE INDEX `fk_parameters_parametersTypes1_idx` ON `parameters` (`typeID` ASC);

Now we have ITEMS connected to their groups and parameters, so we have something like a catalog with electronics parts. At this moment we can search for elements of interest, but we don't know if we have it already. To add possibility to store information about how many items are at my (USER) desk so I create a tables called...

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `magazines` (
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  `description` VARCHAR(512) NULL,
  `user_ID` SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_magazines_users1`
    FOREIGN KEY (`user_ID`)
    REFERENCES `users` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE UNIQUE INDEX `ID_UNIQUE` ON `magazines` (`ID` ASC);
CREATE INDEX `fk_magazines_users1_idx` ON `magazines` (`user_ID` ASC);


CREATE TABLE IF NOT EXISTS `itemsMagazinesLinks` (
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `itemNum` DOUBLE NOT NULL DEFAULT 0,
  `rackName` VARCHAR(255) NULL,
  `items_ID` MEDIUMINT UNSIGNED NOT NULL,
  `magazines_ID` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_itemsMagazinesLinks_items1`
    FOREIGN KEY (`items_ID`)
    REFERENCES `items` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_itemsMagazinesLinks_magazines1`
    FOREIGN KEY (`magazines_ID`)
    REFERENCES `magazines` (`ID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_itemsMagazinesLinks_items1_idx` ON `itemsMagazinesLinks` (`items_ID` ASC);
CREATE INDEX `fk_itemsMagazinesLinks_magazines1_idx` ON `itemsMagazinesLinks` (`magazines_ID` ASC);

I can connect a ITEM to a MAGAZINE and set the number of them.
I hope everything is clear till now, so let's continue.

We have a magazine with some elements. When I want to check if I have everything to make a radio I have to check it manually, which is very time consuming. So, I come with an idea of SETS which are something like a recipe (take 2 resistors, capacitor and another set (CHILD SET) then weld it together to get a radio). Even more, I want to have a trace of every creation of set in my DB, so I want to have a mechanism that will move items from MAGAZINE to SET and add this SET to MAGAZINE... in other words when I create a SET, my application should save in database information that I take 2 resistors (…) and put it to set with ID xxx, update information about magazine content, create a radio and put it in MAGAZINE as a new set... it will be perfect when my DB does it itself (by a trigger) AND even more, every set should have possibility of having a Serial Number... So I need to store every item separately in magazine table, or hold number of sets in magazine table and in other table hold only the S/N.. I don't know how to do it...
Mentioned tables

CREATE TABLE IF NOT EXISTS `sets` (
  `ID` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(1024) NULL,
  `description` VARCHAR(10240) NULL,
  `groups_ID` SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `fk_sets_groups1`
    FOREIGN KEY (`groups_ID`)
    REFERENCES `groups` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_sets_groups1_idx` ON `sets` (`groups_ID` ASC);

CREATE TABLE IF NOT EXISTS `itemsInSets` (
  `iditemsInSet` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `items_ID` MEDIUMINT UNSIGNED NOT NULL,
  `sets_ID` MEDIUMINT UNSIGNED NOT NULL,
  `quantity` DOUBLE NOT NULL,
  `additionalInfo` VARCHAR(10240) NULL,
  PRIMARY KEY (`iditemsInSet`),
  CONSTRAINT `fk_itemsInSets_items1`
    FOREIGN KEY (`items_ID`)
    REFERENCES `items` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_itemsInSets_sets1`
    FOREIGN KEY (`sets_ID`)
    REFERENCES `sets` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_itemsInSets_items1_idx` ON `itemsInSets` (`items_ID` ASC);
CREATE INDEX `fk_itemsInSets_sets1_idx` ON `itemsInSets` (`sets_ID` ASC);

CREATE TABLE IF NOT EXISTS `childrenSets` (
  `parentSetID` MEDIUMINT UNSIGNED NOT NULL,
  `childSetID` MEDIUMINT UNSIGNED NOT NULL,
  `quantity` DOUBLE NOT NULL,
  CONSTRAINT `fk_table1_sets1`
    FOREIGN KEY (`parentSetID`)
    REFERENCES `sets` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_childrenSets_sets1`
    FOREIGN KEY (`childSetID`)
    REFERENCES `sets` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_table1_sets1_idx` ON `childrenSets` (`parentSetID` ASC);
CREATE INDEX `fk_childrenSets_sets1_idx` ON `childrenSets` (`childSetID` ASC);

Every SET is connected to a group, like normal item (if somebody will create a resistor from wire and stick it will be a set and it will be a normal resistor) and of course it has some parameters. So from 'side' of group everything is clear, but it starts to complicate when I want to add a set to magazine, because table itemsMagazinesLinks holds only a id of item, and id of magazine, when I wish to add a set instead of item... probably I need to create a separate column and fill or item or set, but this will leave always one empty field... or do some other magic.
So the question is how to do that?? Should I add a new column(s) to items table to say that this item is a set?
General how to store those information in database

at end, about why EAV? I read things that you write about EAV and must say that... I want to create a application that will find elements that fit to a query. Example: when I want to create a SET for simple transistor key (transistor driven by µC to do something) the base resistor must have resistance from e.g. 10k to even 100k ohms, my database must find an element which fits.
Other thing is that I split parameters to different eav tables (int's float's and strings) and after some test the performance is sufficient.