For an assignment I have to build an application that is able to store tests. Every test has to be validated by a a different user when created or updated. When it has been validated by an user, the test gets an expiry date of the last time it was validated plus 2 years.
Everytime a test gets updated, it creates a new version:
v3, etc. This versioning information is stored in the
The tests itself are all stored in the same
tests table with a foreign key to the
test_versions table (
table. Thetest` table consists of multiple many-to-many relations with joining-tables. For the sake of simplicity, I didn't include these in my table definition.
create table tests ( id int auto_increment primary key, name varchar(255) not null, description varchar(2048) not null, version_id int not null, is_active tinyint(1) default 1 null comment, created_at timestamp null, updated_at timestamp null ) collate = utf8_unicode_ci;
Every new test is made with a new revision. The
tests table has an
version_id column. This is referring to the
test_versions table which has the following columns:
original_version_id(The test where this version is made off)
is_current_version(An indicator if it's the test's current version)
expiry_date(The expiry date of the current version, after this date, the test would not be showing).
It's possible to have unlimited versions for every test.
Now the problem is that when a new test is created, a new version should also be created in the
test_versions table. This is not possible in the current structure since every new record in the
test_versions table needs an
Dropping, or not working with foreign keys would not be an option.
- How can I create the table structure in a way that I can still have unlimited versions without having the foreign key problems? How does the StackExchange site do it for example?
- In my structure, every new test needs a version. Can this be done any better or is it efficient to split data over multiple tables?
- Could there be another table structure for not working with these FK's in both directions?
- Is it efficiënt to store more versions in the same
teststable? Is there any other/better way?