MySQL Database Design – Two Foreign Keys Referring to One Primary Key

database-designforeign keyMySQLprimary-key

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.

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

Best Answer

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;

Related Solutions

Mysql – Two primary keys to one foreign key

I would suggest that you either want a single "couple" table instead of two, if the properties of each coupling are sufficiently similar, or a couple table that the two existing types of couple "inherit" from, like so:

RC                  Couple                                Egg
----------          ---------------------------           ----------------
C ID (FK)   ----->  C ID (PK)                    <-----   C ID (FK)
(rc props)    |     (generic couple properties)           (egg properties)
              |
BC            |
----------    |
C ID (FK)   --'
(BC props)

You could still have breeding and racing details for any could in this arrangement (which might actually be fine, of course) but each egg can only be associated with one couple. You won't have problems with overlapping ID ranges as there is only one range of IDs for couples when modeled this way, and all other tables with data relating to couples only have the one FK linking to the could record.

Oracle 11g – Creating Composite Primary Key from Multiple Tables

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Best Answer

Related Solutions

Mysql – Two primary keys to one foreign key

Oracle 11g – Creating Composite Primary Key from Multiple Tables

Related Question