0

I'd to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like:

| userID        |    name       |      email       |   balance  |
| ------------- |---------------|------------------|------------|
| 101           | alpha         | alpha@mail.com   |   1000     |
| 102           | bravo         | bravo@mail.com   |    500     |
| 103           | charlie       | charlie@mail.com |   2000     |

And the transaction table should look like:

| transactionID |  from_user    |   to_user        | transfer_amount  |
| ------------- |---------------|------------------|------------------|
|   1           | 101           |       103        |   100            |
|   2           | 102           |       101        |   150            |
|   3           | 102           |       103        |   200            |

I tried to draw a schema with Vertableo which doesn't seem have one to zero or many relationship option. But a user might not send or receive points at all, so the relationship should be one to zero or many. However, this schema on Vertableo throws the error reference name must be unique.

enter image description here

Could someone give hints to draw a schema or provide SQL code?

  • If you tag the question with the RDBMS this is for, then someone might be able to suggest suitable SQL CREATE statements. – dbdemon Apr 5 '18 at 12:05
  • Added your recommended tags. Hoping it helps. – moazzem Apr 5 '18 at 12:10
  • @moazzem: dbdemon wasn't talking about the rdbms tag, but a tag for the database product you are using postgresql, oracle, db2, ... – a_horse_with_no_name Apr 5 '18 at 12:14
  • Oh okay. I'll be using MySQL. Added mysql tag. – moazzem Apr 5 '18 at 14:38
1

Example SQL code (create with MySQL Workbench):

CREATE TABLE `test`.`user` (
  `iduser` INT NOT NULL,
  `username` VARCHAR(45) NULL,
  PRIMARY KEY (`iduser`));


CREATE TABLE `test`.`transfer` (
  `transactionID` INT NOT NULL,
  `from_user` INT NULL,
  `to_user` INT NULL,
  `transfer_amount` FLOAT NULL,
  PRIMARY KEY (`transactionID`),
  INDEX `from_user_key_idx` (`from_user` ASC),
  INDEX `to_user_key_idx` (`to_user` ASC),
  CONSTRAINT `from_user_key`
    FOREIGN KEY (`from_user`)
    REFERENCES `test`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `to_user_key`
    FOREIGN KEY (`to_user`)
    REFERENCES `test`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

This works. However if you want to query the 'transfer'-table and want to substitute the IDs for usernames it gets a bit more complicated. Heres an example query:

select a.username as from_user, b.username as to_user, transfer_amount
from transfer
inner join user a
on transfer.from_user = a.iduser
inner join user b
on transfer.to_user = b.iduser;

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.