Analisando o cenário — que apresenta características associadas ao assunto conhecidas como bancos de dados temporais — sob uma perspectiva conceitual, pode-se determinar que: (a) uma versão “atual” da história do blog e (b) uma versão “passada” da história do blog , embora muito semelhantes, são entidades de diferentes tipos.

Além disso, ao trabalhar no nível lógico de abstração, fatos (representados por linhas) de tipos distintos devem ser retidos em tabelas distintas. No caso em consideração, mesmo quando bastante semelhantes, (i) os fatos sobre as versões “atuais” são diferentes dos (ii) fatos sobre as versões “passadas” .

Portanto, recomendo gerenciar a situação por meio de duas tabelas:

• um dedicado exclusivamente para as Versões “atuais” ou “presentes” das Histórias do Blog , e

• uma que é separada, mas também ligada à outra, para todas as Versões “anteriores” ou “passadas” ;

cada um com (1) um número ligeiramente distinto de colunas e (2) um grupo diferente de restrições.

De volta à camada conceitual, considero que —em seu ambiente de negócios— Autor e Editor são noções que podem ser delineadas como Papéis que podem ser desempenhados por um Usuário , e esses aspectos importantes dependem da derivação de dados (via operações de manipulação de nível lógico) e interpretação (efetuada pelos leitores e escritores do Blog Stories , no nível externo do sistema informatizado de informação, com o auxílio de um ou mais programas aplicativos).

Vou detalhar todos esses fatores e outros pontos relevantes a seguir.

Regras do negócio

De acordo com meu entendimento de seus requisitos, as seguintes formulações de regras de negócios (reunidas em termos dos tipos de entidade relevantes e seus tipos de inter-relações) são especialmente úteis para estabelecer o esquema conceitual correspondente:

• Um usuário escreve zero-um-ou-muitos BlogStories
• Um BlogStory contém zero-um-ou-muitos BlogStoryVersions
• Um usuário escreveu zero-um-ou-muitos BlogStoryVersions

Diagrama IDEF1X expositivo

Consequentemente, para expor minha sugestão em virtude de um dispositivo gráfico, criei um exemplo de IDEF1X, ^um diagrama derivado das regras de negócios formuladas acima e de outros recursos que parecem pertinentes. É mostrado na Figura 1 :

Por que BlogStory e BlogStoryVersion são conceituados como dois tipos de entidades diferentes?

Porque:

• Uma instância de BlogStoryVersion (ou seja, uma “passada”) sempre contém um valor para uma propriedade UpdatedDateTime , enquanto uma ocorrência de BlogStory (ou seja, uma “presente”) nunca o contém.

• Além disso, as entidades desses tipos são identificadas exclusivamente pelos valores de dois conjuntos distintos de propriedades: BlogStoryNumber (no caso das ocorrências de BlogStory ) e BlogStoryNumber mais CreatedDateTime (no caso das instâncias de BlogStoryVersion ).

_{^uma Definição de Integração para Modelagem de Informações ( IDEF1X ) é uma técnica de modelagem de dados altamente recomendável que foi estabelecida como padrão em dezembro de 1993 pelo Instituto Nacional de Padrões e Tecnologia (NIST) dos Estados Unidos. Baseia-se no material teórico inicial de autoria do único criador do modelo relacional , ou seja, Dr. EF Codd ; na visão Entidade-Relacionamento de dados, desenvolvida pelo Dr. PP Chen ; e também na Logical Database Design Technique, criada por Robert G. Brown.}

Layout lógico SQL-DDL ilustrativo

Então, com base na análise conceitual apresentada anteriormente, declarei o design de nível lógico abaixo:

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on your business context characteristics.

-- Also you should make accurate tests to define the most
-- convenient index strategies at the physical level.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions.    

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATETIME NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    UserName        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        BirthDate,
        GenderCode
    ), 
    CONSTRAINT UserProfile_AK2 UNIQUE (UserName) -- ALTERNATE KEY.
);

CREATE TABLE BlogStory (
    BlogStoryNumber INT      NOT NULL,
    Title           CHAR(60) NOT NULL,
    Content         TEXT     NOT NULL,
    CoverImageName  CHAR(30) NOT NULL,
    IsActive        BIT(1)   NOT NULL,
    AuthorId        INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT BlogStory_PK              PRIMARY KEY (BlogStoryNumber),
    CONSTRAINT BlogStory_AK              UNIQUE      (Title), -- ALTERNATE KEY.
    CONSTRAINT BlogStoryToUserProfile_FK FOREIGN KEY (AuthorId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE BlogStoryVersion  (
    BlogStoryNumber INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    Title           CHAR(60) NOT NULL,
    Content         TEXT     NOT NULL,
    CoverImageName  CHAR(30) NOT NULL,
    IsActive        BIT(1)   NOT NULL,
    AuthorId        INT      NOT NULL,
    UpdatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT BlogStoryVersion_PK              PRIMARY KEY (BlogStoryNumber, CreatedDateTime), -- Composite PK.
    CONSTRAINT BlogStoryVersionToBlogStory_FK   FOREIGN KEY (BlogStoryNumber)
        REFERENCES BlogStory (BlogStoryNumber),
    CONSTRAINT BlogStoryVersionToUserProfile_FK FOREIGN KEY (AuthorId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT DatesSuccession_CK               CHECK       (UpdatedDateTime > CreatedDateTime) --Let us hope that MySQL will finally enforce CHECK constraints in a near future version.
);

Testado neste SQL Fiddle que roda no MySQL 5.6.

a BlogStorymesa

Como você pode ver no design de demonstração, defini a BlogStorycoluna PRIMARY KEY (PK para abreviar) com o tipo de dados INT. A esse respeito, você pode corrigir um processo automático integrado que gera e atribui um valor numérico para essa coluna em cada inserção de linha. Se você não se importa em deixar lacunas ocasionalmente neste conjunto de valores, então você pode empregar o atributo AUTO_INCREMENT , comumente usado em ambientes MySQL.

Ao inserir todos os seus BlogStory.CreatedDateTimepontos de dados individuais, você pode utilizar a função NOW() , que retorna os valores de data e hora atuais no servidor de banco de dados no exato instante da operação INSERT. Para mim, essa prática é decididamente mais adequada e menos propensa a erros do que o uso de rotinas externas.

Desde que, conforme discutido nos comentários (agora removidos), você queira evitar a possibilidade de manter BlogStory.Titlevalores duplicados, é necessário configurar uma restrição UNIQUE para esta coluna. Devido ao fato de que um determinado Título pode ser compartilhado por várias (ou mesmo todas as) BlogStoryVersions “anteriores” , uma restrição UNIQUE não deve ser estabelecida para a BlogStoryVersion.Titlecoluna.

Incluí a BlogStory.IsActivecoluna do tipo BIT(1) (embora um TINYINT também possa ser usado) caso você precise fornecer a funcionalidade DELETE “soft” ou “lógica”.

Detalhes sobre a BlogStoryVersionmesa

Por outro lado, o PK da BlogStoryVersiontabela é composto por (a) BlogStoryNumbere (b) uma coluna nomeada CreatedDateTimeque, claro, marca o instante preciso em que uma BlogStorylinha sofreu um INSERT.

BlogStoryVersion.BlogStoryNumber, além de fazer parte da PK, também é restrita como uma FOREIGN KEY (FK) que referencia BlogStory.BlogStoryNumber, configuração que reforça a integridade referencial entre as linhas dessas duas tabelas. Nesse sentido, BlogStoryVersion.BlogStoryNumbernão é necessário implementar a geração automática de a, pois, sendo configurado como FK, os valores INSERIDOS nesta coluna devem ser “extraídos” dos já incluídos na respectiva BlogStory.BlogStoryNumbercontraparte.

A BlogStoryVersion.UpdatedDateTimecoluna deve reter, como esperado, o ponto no tempo em que uma BlogStorylinha foi modificada e, como consequência, adicionada à BlogStoryVersiontabela. Portanto, você também pode usar a função NOW() nessa situação.

O Intervalo compreendido entre BlogStoryVersion.CreatedDateTimee BlogStoryVersion.UpdatedDateTimeexpressa todo o Período durante o qual uma BlogStorylinha esteve “presente” ou “atual”.

Considerações para uma Versioncoluna

Pode ser útil pensar como a coluna que contém o valor que representa uma determinada versãoBlogStoryVersion.CreatedDateTime “passada” de um BlogStory . Considero isso muito mais benéfico do que um ou , pois é mais amigável no sentido de que as pessoas tendem a estar mais familiarizadas com os conceitos de tempo . Por exemplo, os autores ou leitores do blog podem se referir a uma BlogStoryVersion de maneira semelhante à seguinte:VersionIdVersionCode

• “Quero ver a versão específica do BlogStory identificada pelo número que 1750 foi criada em ”.26 August 20159:30

As funções do autor e do editor : derivação e interpretação de dados

Com essa abordagem, você pode distinguir facilmente quem detém o “original” AuthorIdde um BlogStory concreto SELECIONANDO a versão “mais antiga” de um determinado BlogStoryIdFROM da BlogStoryVersiontabela em virtude da aplicação da função MIN() a BlogStoryVersion.CreatedDateTime.

Desta forma, cada BlogStoryVersion.AuthorIdvalor contido em todas as linhas das Versões “posteriores” ou “sucessivas” indica, naturalmente, o identificador do Autor da respectiva Versão em questão, mas pode-se dizer também que tal valor está, ao mesmo tempo, denotando o Papel desempenhado pelo Usuário envolvido como Editor da Versão “original” de um BlogStory .

Sim, um determinado AuthorIdvalor pode ser compartilhado por várias BlogStoryVersionlinhas, mas na verdade essa é uma informação que diz algo muito significativo sobre cada Version , portanto, a repetição desse dado não é um problema.

O formato das colunas DATETIME

Quanto ao tipo de dados DATETIME, sim, você está certo, “ MySQL recupera e exibe valores DATETIME no YYYY-MM-DD HH:MM:SSformato ' ' ”, mas você pode inserir com segurança os dados pertinentes dessa maneira e, quando precisar realizar uma consulta, basta faça uso das funções DATE e TIME incorporadas para, entre outras coisas, mostrar os valores relativos no formato apropriado para seus usuários. Ou você certamente pode realizar esse tipo de formatação de dados por meio do código do(s) seu(s) programa(s) aplicativo(s).

Implicações das BlogStoryoperações UPDATE

Sempre que uma BlogStorylinha sofrer um UPDATE, você deve garantir que os valores correspondentes que estavam “presentes” até a modificação serem inseridos na BlogStoryVersiontabela. Assim, sugiro cumprir essas operações dentro de uma única ACID TRANSACTION para garantir que sejam tratadas como uma Unidade de Trabalho indivisível. Você também pode empregar GATILHOS, mas eles tendem a tornar as coisas desarrumadas, por assim dizer.

Apresentando uma coluna VersionIdouVersionCode

Se você optar (devido a circunstâncias comerciais ou preferências pessoais) por incorporar uma coluna BlogStory.VersionIdou para distinguir as BlogStoryVersions , deverá ponderar as seguintes possibilidades:BlogStory.VersionCode

1. A VersionCode could be required to be UNIQUE in (i) the whole BlogStory table and also in (ii) BlogStoryVersion.

   Therefore, you have to implement a carefully tested and totally reliable method in order to generate and assign each Code value.

2. Maybe, the VersionCode values could be repeated in different BlogStory rows, but never duplicated along with the same BlogStoryNumber. E.g., you could have:

   • a BlogStoryNumber 3 - Version 83o7c5c and, simultaneously,
   • a BlogStoryNumber 86 - Version 83o7c5c and
   • a BlogStoryNumber 958 - Version 83o7c5c.

The later possibility opens another alternative:

3. Keeping a VersionNumber for the BlogStories, so there could be:

   • BlogStoryNumber 23 - Versions 1, 2, 3…;
   • BlogStoryNumber 650 - Versions 1, 2, 3…;
   • BlogStoryNumber 2254 - Versions 1, 2, 3…;
   • etc.

Holding “original” and “subsequent” versions in a single table

Although maintaining all the BlogStoryVersions in the same individual base table is possible, I suggest not to do it because you would be mixing two distinct (conceptual) types of facts, which thus has undesirable side-effects on

• data constraints and manipulation (at the logical level), along with
• the related processing and storage (at the physical tier).

But, on condition that you choose to follow that course of action, you can still take advantage of many of the ideas detailed above, e.g.:

• a composite PK consisting of an INT column (BlogStoryNumber) and a DATETIME column (CreatedDateTime);
• the usage of server functions in order to optimize the pertinent processes, and
• the Author and Editor derivable Roles.

Seeing that, by proceeding with such an approach, a BlogStoryNumber value will be duplicated as soon as “newer” Versions are added, an option that and that you could evaluate (which is very alike to those mentioned in the previous section) is establishing a BlogStory PK composed of the columns BlogStoryNumber and VersionCode, in this manner you would be able to uniquely identify each Version of a BlogStory. And you can try with a combination of BlogStoryNumber and VersionNumber too.

Similar scenario

You may find my answer to this question of help, since I as well propose enabling temporal capabilities in the concerning database to deal with a comparable scenario.

Uma opção é usar a Versão Normal Form (vnf). As vantagens incluem:

• Os dados atuais e todos os dados anteriores residem na mesma tabela.
• A mesma consulta é usada para recuperar dados atuais ou dados atuais em qualquer data específica.
• Referências de chave estrangeira para dados com versão funcionam da mesma forma que para dados sem versão.

Um benefício adicional no seu caso, como os dados com versão são identificados exclusivamente tornando a data efetiva (a data em que a alteração foi feita) parte da chave, um campo version_id separado não é necessário.

Aqui está uma explicação para um tipo muito semelhante de entidade.

Mais detalhes podem ser encontrados em uma apresentação de slides aqui e um documento não totalmente completo aqui

sua relação

(story_id, version_id, editor_id, author_id, timestamp, title, content, coverimg)

não está na 3ª forma normal. Para cada versão de sua história, o author_id é o mesmo. Então você precisa de duas relações para superar isso

(story_id, author_id)
(story_id, version_id, editor_id, timestamp, título, conteúdo, coverimg)

A chave da primeira relação é story_id, a chave da segunda relação é a chave combinada (story_id, version_id). Se você não gosta de chave combinada, pode usar apenas version_idcomo chave