score:3
The long answer is to learn about Time-Oriented Database Applications, there's a web site that has an eBook about it.
The short version is to either have another table for the history or change you existing table to allow for history.
Think about how you would differentiate between "original version", "edited versions" and "most recent versions". It could be simply by date, or you could try to maintain your own version number column, or you might even want some kind of flag or bit field (I don't recommend this option).
UPDATE
One possible solution (hard to know the best one for your scenario, so just an example):
Keep your existing table for the most recent version, as that's what you will probably need most of the time.
Add another table for historical versions, and in code (or a SQL Trigger if you have to) copy the existing version to the history table before saving the update. The schema of your history table could look something like this:
CREATE TABLE [dbo].[NewsHistory]
(
NewsHistoryID int IDENTITY(1, 1) PRIMARY KEY,
NewsID int NOT NULL,
Title varchar(100),
Headline varchar(200),
Content varchar(MAX),
CreatedAt datetime NOT NULL,
CreatedBy int NOT NULL
)
Just populate NewsID, Title, Headline, Content, CreatedAt, CreatedBy
from the existing table before writing an update.
When you want to see the history for a news item, simply:
SELECT
Title,
Headline,
Content,
CreatedAt,
CreatedBy
FROM NewsHistory
WHERE NewsID = @newsID
ORDER BY NewsHistoryID
Sort by date if you prefer, but natural ordering should work here if everything is inserted in the right order. Again, depends on your needs/environment.
Read More
- Database structure for edited news
- Need advice for Database structure (MS SQL / MySQL)
- Recommendations for database structure with huge dataset
- What is the best database structure for this scenario?
- How to introduce new concept into database table structure for 0..1 instances?
- Best way to structure database and program for distance look up
score:0
See, I suppose you want to track article's editing version, So use below schema for that.
NewsId,User,Title,HeadLine,Content,Status,PostType,UpdatedBy,UpdatedDate
Status contains enum like Published,Draft something.! So you can find published posts.
PostType contains enum like Revision,Final so you can find revision of any news article.
UpdatedBy contains user's Id who updates news & UpdatedDate contains date when post is updated.
score:0
I’d go with pretty much identical table structure for history.
Table NewsHistory News_History_ID, News id, Revision, title, headline, content, date, user
News_ID would reference the latest version of the news in news table Revision would be used to make tracking easier
What you can do is to create UPDATE trigger in your news table that will automatically copy current version into NewsHistory table.
More questions
- Best way to track sql server 2008 database structure installed in client for next upgrade
- SQL database structure for Like and DisLike
- Which type of database structure design is better for performance?
- database structure for category and sub category
- News Database Design for a Desktop Application
- How to Structure Database and create API for the same database, in case if single users is a member of multiple country
- How to fetch the row count for all tables in a SQL SERVER database
- Storing JSON in database vs. having a new column for each key
- What resources exist for Database performance-tuning?
- Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'
- How do I search an SQL Server database for a string?
- How to use a variable for the database name in T-SQL?
- How do you check what version of SQL Server for a database using TSQL?
- "The transaction log for database is full due to 'LOG_BACKUP'" in a shared host
- The transaction log for the database is full
- Last executed queries for a specific database
- Get structure of temp table (like generate sql script) and clear temp table for current instance
- Database design for user settings
- Database design for invoices, invoice lines & revisions
- Wait on the Database Engine recovery handle failed. Check the SQL server error log for potential causes
- How do I grant read access for a user to a database in SQL Server?
- Create Duplicate SQL Database for Testing
- INT vs Unique-Identifier for ID field in database
- The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
More questions with similar tag
- What data type should you use when storing weight (in kg) when it includes both decimals and integers with different lengths?
- SQL Server Insert Data From Other table
- sql server - 2 left outer join in stored procedure error
- How to use SQL Server's GETDATE() when system time is off?
- Why is PHP -> MSSQL very slow when the DB is not on the same machine as the PHP code
- How to convert mm/dd/yyyy militarytime -> mm/dd/yyyy hh:mm:ss AM/PM
- Show data in two new columns
- SSRS Report Date Format Switching to US in Visual Studio 2012
- SQL Select Data with condition on multiple rows
- Selecting two tables and ranking
- Calculating run down dates using only percentages using Microsoft SQL Server
- Which API takes higher priority over registerColumnType and registerHibernateType?
- creating a stored procedure from a recursive query
- Need to wait on specific column state change in SQL
- SQL Server - Rows to column based on third column value
- How to concatenate row values for use in WHERE clause of T-SQL query
- SQL - How to count records for each status in one line per day?
- Sql Server - Matching exact ids or more
- Finding Updated Columns from within the Stored Procedures
- SQL Server: store INT in BIGINT column can be a problem?
- SQL Server 2005 DBA Learning Resource
- TSql Preceding Backslash Changes column Value
- The connection to the host server failed. Java
- SQL if no rows are returned do this
- SQL replace statement using variables
- SQL Server xml copy values of one node to another
- Deleting orphans from a table
- Return '0' INT while reading XML attribute in SQL
- How can i run sql server script with c#?
- How do I delete excel rows in SSIS?