Rammehar Sharma Rammehar Sharma - 5 months ago 19
SQL Question

How to build database table structure for customer service products in mysql php?

I am building a cable customer project database structure in this project customers(subscribers) use service products like (settopbox, modem, etc 3 or 4 service products with different attributes). Below is my database table structure :-

Customer Table

customer_id,name,address, phone_no, etc

customer_settopbox table

customer_modem table

packages table

my question is :-
1. Is it good to build separate table for each device and allot to customer.

or any other method is there for this. because each device has their own attributes like settopbox has VCNO, SerialNo etc modem has other attributes.
I will be vary thankful.

Answer
  • Add all common product fields to product table.
  • All custom fileds can be placed to product_custom_attribute table.

you can use structure like this :

CREATE TABLE `customer` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NOT NULL,
    `address` VARCHAR(255) NULL,
    `phone_no` VARCHAR(10) NULL,
    PRIMARY KEY (`id`)
);


CREATE TABLE `product_type` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`)
);



CREATE TABLE `product` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `type` INT(11) NOT NULL,
    `name` VARCHAR(45) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `customer_id` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK product type  product_type id_idx` (`type`),
    KEY `FK product customer_id  customer id_idx` (`customer_id`),
    CONSTRAINT `FK product customer_id  customer id` FOREIGN KEY (`customer_id`)
        REFERENCES `customer` (`id`)
        ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT `FK product type  product_type id` FOREIGN KEY (`type`)
        REFERENCES `product_type` (`id`)
        ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;




CREATE TABLE `product_custom_attribute` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `product_id` INT NOT NULL,
    `name` VARCHAR(45) NOT NULL,
    `value` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK product_custom_attribute product_id  product id_idx` (`product_id` ASC),
    CONSTRAINT `FK product_custom_attribute product_id  product id` FOREIGN KEY (`product_id`)
        REFERENCES `product` (`id`)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

If you do not familiar with FOREIGN KEYs use this one

CREATE TABLE `customer` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `address` VARCHAR(255) NULL,
  `phone_no` VARCHAR(10) NULL,
  PRIMARY KEY (`id`));


CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK product type  product_type id_idx` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `product_custom_attribute` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `value` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK product_custom_attribute product_id  product id_idx` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `product_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Find customers products :

SELECT 
    *
FROM
    product
        JOIN
    `product_custom_attribute` ON `product_custom_attribute`.`product_id` = `product`.`id`
WHERE
    customer_id = 1