History of data for documents

External Since: Feb 18, 2005 Posts: 5

Hello,

I have following situation:

CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] [uniqueidentifier] NOT NULL ,
[CountryId] [uniqueidentifier] NOT NULL ,
[StreetName] [nvarchar] (100) NOT NULL ,
[StreetNo] [nvarchar] (10) NOT NULL ,
[LocalNo] [nvarchar] (10) NOT NULL ,
[PostalCode] [nvarchar] (20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OwnerContractorId] [uniqueidentifier] NOT NULL ,
[TargetContractorId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderProduct] (
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL ,
[OrderId] [uniqueidentifier] NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[Quantity] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[CultureName] [nvarchar] (20) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO

The Problem is:

Order - needs information about contractor and his address (address is
related to the contractor by one-to-many

relation). Furthermore, the order needs information about products (its
names, which are stored in

separate table - each product can have several names, depending on language).

If a name of a product would change, it will be changed in all orders. But
order is the document and I need to

keep it as it was at the moment of creation (constractor data - names,
address etc. , product data - names etc.).
To do this, I have to design some kind of history of data connection to each
other. It is bad situation to me,

when I change address of some contractor - all orders will have changed
addresses, the proper data connections

will collapse.

My idea is to copy all related data for the record which is edited. For
example if an address for contractor is

changed, the new contractor record is created with new id (guid) and the
same symbol (is unique for all active

records) (old one have flag "history" for example - is deactivated) and the
new address is related to this new contractor record. The same story applies
when a name of product is edited.

This mechanism lets to store correct data for each order in the database -
all documents(orders) are connected to the same data as at the moment of
their creation.
Reports in this case are performed by queries operationg in the "Symbol" of
the contractor - this gives the

possibility to find all orders of one contractor - each having correct
address for the moment of creation of

order.

My question is: is this design corresponding to the "rules of art" somehow?
What are your ideas for solving such problems?

I will be thankful for your opinions

Adam Rozycki & friends

External Since: Feb 17, 2004 Posts: 310

  >> My question is: is this design corresponding to the "rules of art"
  >> somehow?

No

  >> What are your ideas for solving such problems?

I am not sure where you got the idea of using a globally unique identifier
type for every key in your tables.

We all work within existing computational constraints; so no business
segment requires a machine generated global identifier for a table unless
you are researching on such values. Other than the "cool" factor, abuse &
hype, there is nothing simple, pragmatic or meaningful about using a
uniqueidentifier column as a key for Orders or Products table as in your
situation.

One approach to your problem, based on your narratives, can be like:

CREATE TABLE Orders (
Order_nbr INT NOT NULL PRIMARY KEY,
Product_id INT NOT NULL,
REFERENCES Products ( Product_id )
Contractor_id INT NOT NULL
Address_id INT NOT NULL,
REFERENCES Contractors ( Contractor_id, Address_id )
... ) ;

CREATE TABLE Contractors (
Contractor_id INT NOT NULL,
Address_id INT NOT NULL,
REFERENCES Addresses ( Address_id ) ,
Name...
PRIMARY KEY ( Contractor_id, Address_id )
) ;

CREATE TABLE Addresses (
Address_id INT NOT NULL PRIMARY KEY,
Address VARCHAR( 40 ) NOT NULL,
City_state_zip VARCHAR( 40 ) NOT NULL,
UNIQUE ( Address, City_state_zip )
... ) ;

CREATE TABLE Products (
Product_id INT NOT NULL PRIMARY KEY,
Product_name ...
) ;

To keep track of history of change in Product names use another table with
temporal datatypes like:

CREATE TABLE ProductHistory (
Product_id INT NOT NULL,
Product_name VARCHAR ( 100 ) NOT NULL,
Assigned_date DATETIME NOT NULL,
Withdrawn_date DATETIME NOT NULL,
...
PRIMARY KEY ( Product_id, Assigned_date )
CHECK ( Withdrawn_date >= Assigned_date )
);

The same approach can be used for changes in other attributes like symbols
which you mentioned as well.

--
Anith