in simple words it's about versioning at record level.
Example
TableEmployee - EmployeeId, EmployeeName,
EmployeeAddress, DepartmentId,
TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,
validity
TableDepartment - DepartmentId, Department
TableDesignation - DesignationId, designation
Via Modify-Employee-Details screen following are editable
EmoyeeName
EmployeeAddress
Department
Designation
this screen should allow user to navigate through changes history.
Example :
Version -1
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Accountant
Version -2
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Chief Accountant - changed
Version -3
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Sales - changed
Designation Marketing Manager - changed
Question :
What is the best proposed database design for maintaining history
records bound with version and retrieval technique
Best Regards
Sasanka 1 1505
Sasanka Pinidiya (pi******@yahoo.com) writes: in simple words it's about versioning at record level.
Example
TableEmployee - EmployeeId, EmployeeName, EmployeeAddress, DepartmentId, TableDesignationMap - EmployeeId, DesignationId, EffectiveDate, validity TableDepartment - DepartmentId, Department TableDesignation - DesignationId, designation
Via Modify-Employee-Details screen following are editable EmoyeeName EmployeeAddress Department Designation ... Question : What is the best proposed database design for maintaining history records bound with version and retrieval technique
There are several techniques, and which is the best depends on the actual
business case.
One techniques which makes it very simple to retrieve the data at a
certain date, is store one version each day. Clearly, this technique
is not appropriate here. This technique is good for things that changes
about daily as for instance currency prices.
Another technique is store key, date and then what changed. But this
makes is difficult to retrieve the complete information at a given point
in time.
So the best technique seems to be to have a table with the same columns
as the base table, and with a datetime column added to hold the time
of the change.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: John |
last post by:
I'm developing an application for medical use that will be used to
capture patient background and visit data. The application will have
approximately 50 forms, with an average of about 20 fields each-- so a
total of 1000+ fields. Almost always, the fields are unique to a
particular form--and it will be pretty unlikely that new forms and...
|
by: Opa1 |
last post by:
I am designing and product ordering database and have a design
question.
I have a Payments table which records all payments for a given
OrderID.
There are several payment methods (cash, credit card, check). Each of
these
payment methods has their own fields (i.e. CreditCardNum for payments
by credit card, CheckNum for payments by check,...
|
by: toedipper |
last post by:
Hello,
I am designing a table of vehicle types, nothing special, just a list of
unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor
etc etc
For the table design I am proposing a single column table with a field name
called vehicle_type and this will contain the vehicle type.
Sot it will be
|
by: Zandy Marantal |
last post by:
Hello everyone,
I'm having trouble using Xerces2(2.4, 2.5) when validating against an
XML schema if a general entity reference is defined within the XML
file.
The error I'm getting is this: "org.xml.sax.SAXParseException: Element
type "personnel" must be declared."
Xerces1(1.4.3, 1.4.4) doesn't have an issue with this. As is XML Spy.
|
by: sk |
last post by:
I have an applicaton in which I collect data for different parameters
for a set of devices. The data are entered into a single table, each
set of name, value pairs time-stamped and associated with a device.
The definition of the table is as follows:
CREATE TABLE devicedata
(
device_id int NOT NULL REFERENCES devices(id), -- id in the...
| |
by: Mark H. |
last post by:
Hello ppl,
I d like some help designing a customized contact list.
I have a table for people and a table for addresses.
In 99% of the cases one person will have one or more addresses so we have 1
to many there.
In very very rare cases one address will belong to more than one person. For
example, a husband and wife may be individual...
|
by: MP |
last post by:
Greets,
context: vb6/ado/.mdb/jet 4.0 (no access)/sql
beginning learner, first database, planning stages
(I think the underlying question here is whether to normalize or not to
normalize this one data field - but i'm not sure)
:-)
Background info:
|
by: JohnH |
last post by:
I'm just recently come to work for an auto brokerage firm. My
position involves performing mysterious rites, rituals and magick in
order to get information out of their access database. This is due to
the fact that the "designer" they hired had an unfortunate ability to
tink around with a program far beyond her reach. She had no concept
of...
|
by: AMDRIT |
last post by:
I am looking for better concrete examples, as I am a bit dense, on design
patterns that facilitate my goals. I have been out to the code project,
planet source code, and microsoft's patterns and practices site and it just
isn't sinking in all that clearly to me.
Currently we have code in production and it all works well, however it is...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |