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         | [email protected]   |   1000     |
| 102           | bravo         | [email protected]   |    500     |
| 103           | charlie       | [email protected] |   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?

4
  • If you tag the question with the RDBMS this is for, then someone might be able to suggest suitable SQL CREATE statements.
    – dbdemon
    Commented Apr 5, 2018 at 12:05
  • Added your recommended tags. Hoping it helps. Commented Apr 5, 2018 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, ...
    – user1822
    Commented Apr 5, 2018 at 12:14
  • Oh okay. I'll be using MySQL. Added mysql tag. Commented Apr 5, 2018 at 14:38

1 Answer 1

2

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 and acknowledge you have read our privacy policy.

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