7
\$\begingroup\$

I'm creating a schema design for a user profile and transactions.

I am very new in data modeling and currently this is my design. The schema has multiple deposit table because the application I'm working on will use different payment gateways for money transfer and their transaction details is different.

EER Diagram from MySQL Workbench

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `test` ;

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 ;
USE `test` ;


-- -----------------------------------------------------
-- Table `test`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`user` ;

CREATE TABLE IF NOT EXISTS `test`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(45) NULL,
  `status` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`profile`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`profile` ;

CREATE TABLE IF NOT EXISTS `test`.`profile` (
  `firstName` VARCHAR(255) NULL,
  `lastName` VARCHAR(255) NULL,
  `middleName` VARCHAR(255) NULL,
  `mobileNumber` VARCHAR(255) NULL,
  `userId` INT NOT NULL AUTO_INCREMENT,
  UNIQUE INDEX `userId_UNIQUE` (`userId` ASC),
  CONSTRAINT `fk_profile_user1`
    FOREIGN KEY (`userId`)
    REFERENCES `test`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`transaction`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`transaction` ;

CREATE TABLE IF NOT EXISTS `test`.`transaction` (
  `id` INT NOT NULL,
  `type` ENUM('DEPOSIT', 'WITHDRAWAL') NULL,
  `status` VARCHAR(45) NULL,
  `userId` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_transaction_user1_idx` (`userId` ASC) VISIBLE,
  CONSTRAINT `fk_transaction_user1`
    FOREIGN KEY (`userId`)
    REFERENCES `test`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`gateway_1_deposit`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`gateway_1_deposit` ;

CREATE TABLE IF NOT EXISTS `test`.`gateway_1_deposit` (
  `requestId` VARCHAR(255) NULL,
  `attribute1` VARCHAR(255) NULL,
  `attribrute2` VARCHAR(255) NULL,
  `transactionId` INT NOT NULL,
  CONSTRAINT `fk_gateway_1_deposit_transaction1`
    FOREIGN KEY (`transactionId`)
    REFERENCES `test`.`transaction` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`gateway_2_deposit`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`gateway_2_deposit` ;

CREATE TABLE IF NOT EXISTS `test`.`gateway_2_deposit` (
  `referenceCode` VARCHAR(255) NULL,
  `attribute1` VARCHAR(255) NULL,
  `attribrute2` VARCHAR(255) NULL,
  `transactionId` INT NOT NULL,
  CONSTRAINT `fk_gateway_2_deposit_transaction1`
    FOREIGN KEY (`transactionId`)
    REFERENCES `test`.`transaction` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

As you can see, the user table has a 1:1 relationship with profile table and 1:0-M relationship with transaction table.

The transaction as a base table manages different types of transaction; has a 1:1 relationship with gateway_1_deposit and 1:1 relationship with gateway_2_deposit

Any insight as to how I can improve or change this to comply with best practices?

\$\endgroup\$
2
  • \$\begingroup\$ In addition to your diagram, can you show the full SQL defining your tables? \$\endgroup\$
    – Reinderien
    Oct 3 at 13:44
  • \$\begingroup\$ @Reinderien hi, updated the post to add the script \$\endgroup\$
    – 9years
    Oct 3 at 13:56
6
\$\begingroup\$

The correct data model often depends on the use-cases that you want to support. Looking at your model there are several questions that spring to mind, which you might want to consider.

  • There's a 1:1 mapping between user and profile, what are you trying to achieve by spreading the information across two tables?
  • Why is email in the user table, rather than the profile table?
  • People often have more than one mobile number, do you need to support different concurrent values (i.e. home/work mobile)? Do you need to support history (current mobile, previous mobile)?
  • Your gateway_deposit tables have almost the same information in them. The difference is really requestId vs referenceCode, which arguably represent the same information. Do these need to be separate tables, or would it be better for them to be a single table with a discriminator column for the type of gateway?
  • transactions has a type, which is constrained to WITHDRAWAL or DEPOSIT, do you need a column to differentiate between the type of gateway used? If not, how are you anticipating fetching back transactions, only from particular gateways, or all transactions?
  • All deposit/withdrawal transactions seem like they're going to have a value, however there's no value field in the transactions table. Is this part of the gateway tables, or are you not interested in this? Is there a need for non-value transaction types in the future?
\$\endgroup\$
3
\$\begingroup\$

Overlapping somewhat with the other answers:

  • status should be constrained, either ideally as an enum, or if that's not possible, then a check constraint
  • mobileNumber having a max length of 255 is arbitrary and doesn't make much sense. If you do want to constrain the length, a shorter constraint would be appropriate.
  • Why no action? This defeats the purpose of referential integrity.
  • Why is transaction.type nullable? It should probably always have a valid value, though it's difficult to say without hearing about the purpose of the table.
  • Since the only difference between the two gateway deposit tables is the reference code versus request ID, you can factor out common columns to a different table. If they're guaranteed to co-exist with transaction at the same cardinality, you can move them to transaction. Otherwise you can create a new table.
\$\endgroup\$
3
\$\begingroup\$

To me it's not clear why user and profile are two different tables. Seems to me that you could combine all fields into one single user table. For contact addresses though, you can have a separate table with a one to many relationship but your model doesn't have that and maybe you don't need that.

In table user the status column could probably be an ENUM like in type in transaction table, with a default value of 'inactive' or something like that, until the user is enabled for example by confirming the E-mail address. In table transaction, status could also be an ENUM.

Will users be able to log in to some website ? I don't see any password column.

Maybe something is missing in your schema: a table of orders. The transaction should relate to the delivery of specific products/services, that should be itemized somewhere. And probably invoices too.

Regarding gateway_1_deposit and gateway_2_deposit: there is almost no difference. So you just need one common table, with an additional field to store which of the two gateways was used. referenceCode and requestId refer to the same thing.

Thus, transaction, gateway_1_deposit and gateway_2_deposit can be merged into one single table named 'transaction'.

Typo in gateway_1_deposit and gateway_2_deposit: attribrute2

I recommend to avoid mixed case in object names, but instead use lowercase with underscore to separate keywords along these guidelines.

Data length choice is somewhat unusual: why VARCHAR(45) and not just 50 which is more common and a rounded value ? I guess 50 characters should be enough except for edge cases (my current job E-mail already has 33 characters and some colleagues have longer or multiple names).

On the other hand VARCHAR(255) for name, surname or mobile number etc is overkill.

In table user and transaction, id and userId can be declared as INT UNSIGNED.

By default, indexes in Mysql are visible, so it's not necessary to specify that.

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