Help designing database

[houssam_ballout]

Hello all,I am trying to design a new sql database with the following requirements: I had items like Pants, T-shirts, Polo (2 kinds) , their status : New or Used , Sizes: S, M, L, XL, XXL , amountBelt, Hat: don't had sizes, only had Status & amountAlso, I need to record when a pant with size S had been submitted to someone (some person, don't needed to be recorded individually as a table) & when, for example, a Polo with size L had been returned..Any help is appreciated!Thanks in advance

[boen_robot]

So where's the problem? You've said it yourself basically:A single table for all item types (Pants, T-Shirts, Polos, Belts, Hats) and/or kinds (not just for the sake of the two polo kinds, but what if you get different kinds of T-Shirts or pants later on?)A table with all sizes.A table associating item kinds with their size (both as a primary key, because you only need to declare the amount once per kind/size combo; use NULL for non-sizable items or a separate table without this column), plus the amount. If this is supposed to be a store catalog, you could also add the price to this table.Here's one such example, created in MySQL workbench with some trial&error (not to be taken as a definitive answer):

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';CREATE SCHEMA IF NOT EXISTS `TEST_STORE` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;SHOW WARNINGS;USE `TEST_STORE`;-- ------------------------------------------------------- Table `TEST_STORE`.`item_types`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `TEST_STORE`.`item_types` (  `itemTypeID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `itemType` VARCHAR(45) NOT NULL ,  `itemKind` VARCHAR(45) NULL ,  PRIMARY KEY (`itemTypeID`) )ENGINE = InnoDB;SHOW WARNINGS;CREATE UNIQUE INDEX `itemTypeID_UNIQUE` ON `TEST_STORE`.`item_types` (`itemTypeID` ASC);SHOW WARNINGS;-- ------------------------------------------------------- Table `TEST_STORE`.`sizes`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `TEST_STORE`.`sizes` (  `sizeID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,  `size` VARCHAR(10) NULL ,  PRIMARY KEY (`sizeID`) )ENGINE = InnoDB;SHOW WARNINGS;CREATE UNIQUE INDEX `sizeID_UNIQUE` ON `TEST_STORE`.`sizes` (`sizeID` ASC);SHOW WARNINGS;-- ------------------------------------------------------- Table `TEST_STORE`.`items`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `TEST_STORE`.`items` (  `itemTypeID` INT UNSIGNED NOT NULL ,  `sizeID` INT UNSIGNED NOT NULL ,  `amount` INT NULL ,  `price` DECIMAL NULL ,  PRIMARY KEY (`itemTypeID`, `sizeID`) ,  CONSTRAINT `fk_items_item_types`	FOREIGN KEY (`itemTypeID` )	REFERENCES `TEST_STORE`.`item_types` (`itemTypeID` )	ON DELETE CASCADE	ON UPDATE CASCADE,  CONSTRAINT `fk_items_sizes1`	FOREIGN KEY (`sizeID` )	REFERENCES `TEST_STORE`.`sizes` (`sizeID` )	ON DELETE CASCADE	ON UPDATE CASCADE)ENGINE = InnoDB;SHOW WARNINGS;CREATE UNIQUE INDEX `itemID_UNIQUE` ON `TEST_STORE`.`items` (`itemTypeID` ASC);SHOW WARNINGS;CREATE UNIQUE INDEX `sizeID_UNIQUE` ON `TEST_STORE`.`items` (`sizeID` ASC);SHOW WARNINGS;CREATE INDEX `fk_items_item_types` ON `TEST_STORE`.`items` (`itemTypeID` ASC);SHOW WARNINGS;CREATE INDEX `fk_items_sizes1` ON `TEST_STORE`.`items` (`sizeID` ASC);SHOW WARNINGS;SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[houssam_ballout]

Yeah, I know creating tables are not hard.But, I need more help regarding the delivery & receiving of such an item!

[Synook]

Well, the database doesn't handle that, the application interfacing with the database does. Have you decided what language you want to write your application in, and what platform it will use?