I am wondering what kind of table should the columns create_time and update_time be added to?
If I have video table , video_detail table,tag table and tag_video_relation table.
I know tables (such as tag``tag_video_relation) do not need create_time and update_time column.But what about video and video_detail?
If I have one million rows in video table, create_time and update_time would cost a lot of storage.
So, in what situations and in what time we need create_time and update_time in a table?
I've found that its best to add the fields to all tables as they usually become useful down the road.
They will help you investigate issues and problems and identify when they occurred. They are invaluable in diagnosing and debugging issues.
You can see this approach in frameworks like Ruby On Rails where the default template ('skeleton') for a new model/table includes created_at and updated_at automatically.
As for tag_video_relation, not needing create_time and update_time - actually I think they are useful column to have for this kind of relationship. In many cases the ability to know when the relationship was created is useful information to store. An 'updated_at' column for the relation might be one case where you DON'T have the update field, if relations are only either created or deleted.
In terms of storage, I believe this is a trivial concern that is usually not an issue with modern rdmbs's such as Oracle, SQL Server and Postgres, They are intended to allow the rapid retrieval of information from millions of rows, from thousands of tables.
I've worked for many years in applications (both older and newer technologies) and data warehousing and the trend of adding the fields frequently to many tables has led me to form this opinion. It was a personal practice of mine to just add it to all tables I worked with, The fact that framework such as rails use this concept now seems like good validation of my own practice.