Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    1

    Keeping versions of a record

    Hi,

    I'm fairly new to databases and database design - I'm wondering the best way of implementing a method of versioning a record.

    I'm designing a database for a simple document management application. The database has users who create and edit documents as well as adding comments to the docs. When a document is updated, I would like the old version to be stored. There will be an option to recover an old version.

    I have a possible solution:

    User - id, first_name, last_name, etc...
    Primary key: id

    Document - id, version_number, file_name, description
    Primary keys: id, version

    Comment - doc_id (foreign key), comment_number, comment_by_user (FK)
    Primary key: doc_id, comment_number

    Is this a good solution? All the documents and their old versions are stored in the same table. To locate the the current document you would use version 0. The comments are non-version specific, which I think right.

    Will this table design slow down normal searching for records? I guess compared to a table which just had the current documents it would be slower...

    Any comments welcome, thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You seem to be missing a table, as your Comment table's foreign key (doc_id) doesn't have a parent PK to reference - it cannot reference part of the 2-col PK of document.

    Perhaps it should be more like:

    create table document (doc_id primary key, ...);
    create table document_version (doc_id, version_number, ... primary key(doc_id, version_number));
    create table comment (doc_id references document, comment_number, ...);

    This model seems reasonable. The only kind of searches that would be slowed down would be those that cannot use an index to locate the rows they require but instead must perform a full scan of the table. If a typical document has many versions, and a typical search involves scanning the text of only the current versions of each document, then there may be a case for separating current and past versions into separate tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •