Version control independent records and list them all base on a configuration

Question

I have read this two questions:

Is there a MySQL option/feature to track history of changes to records?

How to version control a record in a database

I understood how the version system should work but I have a a particular question for my situation.

For example I have this table:

Let us say that this table has around 4000 records in it. I will display to the user 100 records once based on a preset configuration, for example display all record where record A value is foo.

The user now has the possibility to change any of the 100 records, for example let us say that he changes 4 records and he leaves the other 96 records untouched.

My question is:

If the user changes only 4 record from the preset configuration what is the best way to keep track of the changes and beside that the track of configurations (the way that the 100 record looked like in a particular date before changing).

I can add a start_date and end_date fields to keep track of the configurations in another table but it doesn't feel right to have a table and fill it with 100 record from which only 4 record changed just to be able to know how the configuration looked at a certain date and what record changed according to the version from that date. At the end I will end up with hundreds of duplicated content that has only the date field different. What is the ideal solution for this situation?

---

Later Edit: The main idea is to obtain something like this:

I want to be able to see each configuration version (version 1, 2, 3, 4) from the specific creation date. Each configuration is containing old rows (from previous configuration version) + the rows modified by the user in the new version.

Answer 1

Based on our chat discussion, and this link as a talking point,

consider the following schema and expand upon it.

-- drop table bom
create table bom
(   -- Bill of Materials
    bomId int auto_increment primary key
    -- other high level fields
);

-- drop table bomVersion
create table bomVersion
(   -- Bill of Materials / version
    id int auto_increment primary key,
    bomId int not null,
    -- other high level fields
    version int not null, -- you need to figure out how to increment this, and it is not an auto inc here
    description varchar(1000), -- ie: let's add a Floppy Drive
    creationDate datetime not null,
    unique key(bomId,version),  -- no dupes
    -- yes, (bomId,version) could be the PK but I chose not to
    CONSTRAINT fk_version_bom FOREIGN KEY (bomId) REFERENCES bom(bomId)
);

-- drop table bvDetails;
create table bvDetails
(   -- Bill of Materials / Version / Details
    id int auto_increment primary key,
    bvId int not null,
    lineNumber int not null, -- if ordering is important
    partId int not null,
    qty int not null,   --  I am no BOM expert, is this stuff in there?
    price decimal(12,2) not null, --    I am no BOM expert, is this stuff in there?
    -- FK constraints back to Part table and bvId, below shows one of them
    CONSTRAINT fk_det_bomversion FOREIGN KEY (bvId) REFERENCES bomVersion(id)
);

One of the biggest challenges is how to capture the changes made in Part descriptions if they change. So in that link at the very top, if that Case SX1040 has a change in description from Easy Access to Easy Access / Well vented.

So in that case a re-print of a BOM (that was supposed to be nailed down by ISO standards) is going to change. That is not good. So you need to have an audit, a history, of changes to rows that are textual, and save those ids (like for the Part Number). So to be clear, though you can have a Parts table, also have a PartsHistory table (and the id's from the latter go in the bom).

The numerics like price and qty are cool to save like in the above schema. It is the textual history changes that is problematic, and you need to solve that as described in the previous paragraph.

---

Note, I once wrote a system where in the case of changes to the text columns, we would keep all the revisions all in the same table and have only 1 row (say, for that part) marked as active='Y' for any given item. This way a join was not necessary to the other history table. Either way, you have flexibility from your GUI to select which version you want. Remember from an audit standpoint, you need to have an updateBy (personId) and an updatedDt in these tables.

Edit

Your question just changed. See new column in table bomVersion