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:
- Have Fields A,B,C in Table2
- 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.