I want to save one dataset within another table, this dataset should be persistent.
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.