Billing and recurrent payment db structure I am designing my first payment service and here is my db structure.

Each user can have many businesses, each business can have many products. Some are recurring, some are paid once. Each product is tied to payment method. Some products are always free, and some are paid every X days.

CREATE TABLE `payment_method` (
  `payment_method_id` int(11) NOT NULL auto_increment,
  `payment_method_name` VARCHAR(20) DEFAULT NULL,
  `payment_method_status` ENUM('active','disabled') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'active',
  PRIMARY KEY  (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `product_type` (
  `product_type_code` varchar(50) NOT NULL,
  `product_type` varchar(150) NOT NULL,
  `product_type_name` varchar(150) NOT NULL,
  PRIMARY KEY (`product_type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_price` decimal(12,2) DEFAULT NULL,
  `product_duration` int(11) DEFAULT NULL,
  `product_currency` varchar(50) NOT NULL,
  `recurring` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'yes',
  `daily_limit` int(11) DEFAULT NULL,
  `monthly_limit` int(11) DEFAULT NULL,
  `lifetime_limit` int(11) DEFAULT NULL,
  `product_type_code` varchar(50) NOT NULL,
  `product_description` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  FOREIGN KEY (product_type_code) REFERENCES product_type(product_type_code)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `product_detail` (
  `product_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `key` varchar(150) DEFAULT NULL,
  `value` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`product_detail_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `business_product` (
  `business_product_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `business_id` int(11) DEFAULT NULL,
  `added_by_user_id` int(11) NOT NULL,
  `product_price` decimal(12,2) DEFAULT NULL,
  `product_currency` varchar(5) NOT NULL,
  `product_type` varchar(150) NOT NULL,
  `business_product_status` enum('paid','canceled') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `last_update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`business_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE `user_authorized` (
  `user_authorized_id` int(11) NOT NULL auto_increment,
  `payment_method_id` int(11),
  `authorized`  varchar(150),
  `autopay` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'yes',
  PRIMARY KEY  (`user_authorized_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE `billing` (
  `billing_id` int(11) NOT NULL auto_increment,
  `external_id` VARCHAR(100) DEFAULT NULL,
  `invoice_date` datetime,
  `bill_date` datetime,
  `start_date` date,
  `end_date` date,
  `payment_method_id` int(11) default NULL,
  `user_id` int(11) default NULL,
  `authorized` VARCHAR(200) DEFAULT NULL,
  `fee` decimal(12,2) default NULL,
  `notes` varchar(500) default NULL,
  `billing_status` ENUM('new','paid','returned','canceled', 'pending', 'overdue')  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'new',
  PRIMARY KEY  (`billing_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;


CREATE TABLE `billing_detail` (
  `billing_detail_id` int(11) NOT NULL auto_increment,
  `billing_id` int(11) NOT NULL,
  `product_id` int(11),
  `business_id` int(11),
  `product_price` decimal(12,2),
  PRIMARY KEY  (`billing_detail_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

```


What do you think am I missing something?
Consistency


I like the naming convention of the auto-incrementing primary keys to be prefixed with the table name. This allows the use of using (keyname) when joining tables.
Some tables are created with IF NOT EXISTS, other aren't. Is there a strategy behind this?


Design


I am missing tables user and business.
Table business_product has a column product_type which overrides that of the referenced table product_type. Is this as designed?
Perhaps table user_authorized should be renamed to user, update the name of the primary key and referenced foreign keys along. (This has an impact on the constraints below)


Constraints


Table product_detail is missing a foreign key to product.
Table business_product is missing a foreign key to product, user and business.
Table billing is missing a foreign key to user and payment_method.
Table billing_detail is missing a foreign key to billing, product and business.
Table user_authorized is missing a foreign key to user and payment_method.