1

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: v1, v2, v3, etc. This versioning information is stored in the test_versions table.

The tests itself are all stored in the same tests table with a foreign key to the test_versions table (version_id)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:

  • version_id
  • description
  • original_version_id (The test where this version is made off)
  • tag (v1, v2, v3, etc.)
  • 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 test_id.

Dropping, or not working with foreign keys would not be an option.

Questions

  • 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 tests table? Is there any other/better way?
  • I'm confused. Conceptually, I'd expect you to have a Tests table, with one record per test, a TestVersions table with one record per version of a test (starting with v1, when a test is created), and a TestApprovals table, dependent on the TestVersions table. It looks like the tests table for which you've given a schema is the second table; is that right? – Jon of All Trades May 29 at 15:09
  • @JonofAllTrades The test table consists of all the information concerened about the test itself. The test_versions about the versioning information yeah. The test approval table isn't important at this moment. – Bas May 29 at 15:20
  • Ah, so the test.version_id field isn't the version ID, it's an FK to the current record for that test in the test_versions table? That could probably be renamed. I'm not sure I see the problem, though; as long as you insert into test first, it's easy to create a record in test_versions at the same time, either in a sproc or a trigger. That'll ensure that you never have a record in the one table without a record in the other (except for in the middle of the transaction, which is why we have transactions). – Jon of All Trades May 29 at 15:27
  • @JonofAllTrades Exactly. test.version_id refers to test_versions.idBut since every test needs a test_version, for storing it's version information, the test record can't be inserted first. I don't understand anything and I'm stuck. – Bas May 29 at 15:31
2
+50

My preference is to handle this with a stored procedure rather than a trigger, but that's an option if you have a lot of legacy code running unwrapped INSERTs.

DELIMITER //
CREATE PROCEDURE CreateTest (Name VARCHAR(255), Description VARCHAR(2048))
    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
        ROLLBACK;
        END;

    START TRANSACTION;

    INSERT INTO test (name, description, version_id) VALUES (Name, Description, 0);
    DECLARE TestID INT;
    SET TestID = LAST_INSERT_ID();

    INSERT INTO test_versions (test_id) VALUES (TestID);
    DECLARE TestVersionID INT;
    SET TestVersionID = LAST_INSERT_ID();

    UPDATE test SET version_id = TestVersionID WHERE id = TestID;

    COMMIT;
    END//
DELIMITER ;

Apologies if I screwed up the MySQL syntax, I've been working in MS SQL 90% of the time for the past ten years.

Note that this code temporarily sets the value of version_id to zero, to satisfy the FK. You'd need a dummy record in test_versions with such an ID. However, the field is updated before the transaction commits, so no queries should ever see this value.


Edit: if you are really concerned about the INSERT performance of this sproc, you could simplify it by giving test_version a composite PK on test_id and version_id, where version_id is a sequence (starting at 1) for each test. You would need to retrieve only one AUTO_INCREMENT value:

DELIMITER //
CREATE PROCEDURE CreateTest (Name VARCHAR(255), Description VARCHAR(2048))
    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
        ROLLBACK;
        END;

    START TRANSACTION;

    INSERT INTO test (name, description, version_id) VALUES (Name, Description, 1);
    DECLARE TestID INT;
    SET TestID = LAST_INSERT_ID();

    INSERT INTO test_versions (test_id, version_id) VALUES (TestID, 1);

    COMMIT;
    END//
DELIMITER ;

As you can see, version_id can be assumed to be 1 for the first record, so there's no need to UPDATE test. You also don't need a dummy record #0 in test_version. However, the broader PK would make indices on test_version slightly bigger. Using a narrow integer for version_id would help.

  • Looks good, is this the most efficiënt table structure for my use case though? – Bas May 29 at 21:50
  • Most efficient is, realistically, an impossible goal. However, I see very little overhead with this design. Unless you're running thousands of inserts per second, I doubt that this procedure is likely to be a bottleneck. – Jon of All Trades May 30 at 13:37
  • Well, I don't like the needed FK in both directions. I only would be able to insert records using the transactions. Isn't there an alternative for that? – Bas May 30 at 17:03
  • Bidirectional FKs are a little sticky; at times, I'm happy to not use them, in a reporting database (they're indispensable in a transactional database). If you drop the FK in test, or the entire test.version_id field, you won't need the dummy record, but that's a pretty small gain. – Jon of All Trades May 30 at 18:17
  • 1
    Sure. For any given group n of DBAs or database developers, you'll probably get n+1 possible solutions. You could have a single table with all versions, one of which is marked "current"; a new record would be created with a single INSERT operation, but your table would be big and denormalized. You could have one table of current versions, and a second table with old versions. You can look up "slowly changing dimension" for various strategies on managing this. I've offered one solution which is simple to implement and works with your schema as described, but you could re-engineer more. – Jon of All Trades Jun 3 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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