3
\$\begingroup\$

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?
\$\endgroup\$
  • 1
    \$\begingroup\$ Thanks for joining Code Review.SE and asking a clear question. It would be nice to have an ERD or clearer statement of the business requirements to provide helpful context for the code review for future questions you might do. \$\endgroup\$ – chicks Jun 30 at 10:57
  • \$\begingroup\$ I also cannot review this until I know what it is going to be used for. Saying it is a "payment service" is super vague. Are these businesses paying or do they get paid? No taxes? \$\endgroup\$ – KIKO Software Jun 30 at 12:37
  • \$\begingroup\$ Hello, we are getting paid. We have one off products and recurring products \$\endgroup\$ – find it now Jul 1 at 2:28
1
\$\begingroup\$

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.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.