0

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.

1
  • I'm having trouble understanding what you're trying to achieve -- and why. Can you show us some example data to explain it? Jun 1, 2014 at 15:24

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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