Mysql – Save dataset within another table best practice

foreign keyMySQL

I want to save one dataset within another table, this dataset should be persistent.

For example 

Table1: FIELDS (A,B,C)
Table2: FIELDS (D, DuplicateTable1)

I can think of 2 possiblities:

  1. Have Fields A,B,C in Table2
  2. Make an invisible copy (for example by adding a "hidden" Field in table1) and us it in Table2 as Foreign Key.

I feel both are not very clean. How would you solve this problem ?

Edit: More Specific example:

CREATE TABLE `Bill` (
    `billId` INT(11) NOT NULL AUTO_INCREMENT,
    `to` INT(11) NOT NULL,
    `value` INT(11) NOT NULL,
    PRIMARY KEY (`billId`),
    INDEX `PersonKey` (`to`),
    CONSTRAINT `PersonKey` FOREIGN KEY (`to`) REFERENCES `Person` (`personId`)
)
ENGINE=InnoDB;

CREATE TABLE `Person` (
    `personId` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `address` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`personId`)
)
ENGINE=InnoDB;

In this example the problem is that the bill shouldn't change when the person changes name or address. And I'm looking for a nice way to fix this. I could add all my fields in Person to my Bill table (for example Bill.person_address). But if I add a new field to Person i would also have to change the structure of Table (wich makes the software less scaleable). Or the other way i could think off, is adding a new field "locked" to Person like 

`locked` TINYINT(1) NULL

And when i save a bill I copy the row from Person with locked = 1, then i would change the application that all the locked persons don't show up anywhere else.

Best Answer

If I understand well, you need the bill to still have the person's data when it was created. And if the person changed it's data, like address, you need the bill to keep having the original address where that person was billed.

In other words, you will have different 'versions' of the person. What I would is the following:

Divide person table into two tables with master-details (or one to many) relationship. In the master table, have the fields that do not change, like ID, Name. In the details table, have a primary key field and the ID from master and other fields that may change, like address.

Person(ID primary key, name , ...)
Person_det(ID PK, Person_id referencing person.id, address, ...)

'Bill' table now would have "person_det.id" as a foreign key, instead of person.id.

Related Question