Database programming in a disconnected environment

This is a pre-release post!

How many time have you encounter with this problem? During the course of my carrier 99.9% of the time I need to write database application in a disconnected manner. I decided to write this up and if you got some gut to try it as your assignment ;)

$diagram_place_holder$

Scope:

• Application that run on a single workstation
• Be able to consolidate data from multiple data from the workstation (incremental dataset)
• Allow DBA to consolidate & tracking changed records from each station (for various reasons).

that sound simple enough, now let talk about the infrastructure:

• No WAN available (CRUD process on a single machine)
• Each station rely on slow e-mail connection to submit update dataset

Technology:
Let try to solve this problem base on OOP language

• ASP.NET 2.0 (? I hope one day all station got will be on WAN)
  
• Data Transfer Object (DTO) + use internally by DAL
• Implement Data Access Layer (DAL) + support Import/Export functionality.
  
• Business Logic Layer (BLL) -- interact from user interface (form), use DAL internally

Data Structure Convention:

• Avoid using composite primary key
• Recommended field name:
  IsActive = T/F,
  IsDeleted = T/F, -- logical deletion
  CreatedBy NVARCHAR(50),
  CreatedOn DATETIME,
  ModifiedBy NVARCHAR(50),
  ModifiedOn DATETIME -- using this field to extract data into DTO object
• DataSyn table to store the status of data extraction
• Obsolated table to store deleted record of each table.