History of data for documents

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>> My question is: is this design corresponding to the "rules of art"
No
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 "" 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|||
"Anith Sen" wrote:

> I am not sure where you got the idea of using a globally unique identifier
> type for every key in your tables.
My idea was to single keys instead of complex ones.

> 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.
The reason why I used GUIDs is that this database has to be replicable and
data exchangable with several separate databases. There is to be one master
DB and several slave DBs. Since data can be added in some independent places
- I think I need to use the global identifiers.
My problem is that I have to have a data corresponding to Order, just the
same as it was at the moment of creating of Order. I cannot have situation
when changed contractor data changes data in all orders.
The database has to store names for products in several languages, since
that I cannot put all information about products in one table.
I would like to achieve rather some sort of document revision than history
of action on documents.
Adam|||>> My idea was to single keys instead of complex ones.
Good. Simple keys are a recommended consideration for a primary key.
However, having a uniqueidentifier in a table as the only key in your table
does nothing for entity identification, which is the main purpose of a key
in the first place.
Not necessarily. You could opt for any arbitrary namespace to determine
independent databases distributed over different servers. While
uniqueidentifier type guarantees the value to be unique globally, it cannot
guarantee the uniqueness of the corresponding entity.
For instance, a customer by name Adam in a table in database A cannot be
distinguished from another customer by same name Adam in similar table in a
replicable database B or even in the same table in the same database, just
by virtue of arbitrary GUIDs alone. All you'll have is duplicated entries of
Adam in the table with different GUIDs associated with them. How do you
identify the row corresponding to Adam? How will you enforce entity
integrity? How do you track down an alleged error, for instance in data
entry? Can you use GUIDs for referencing keys reliably without cascading
changes?
It is mostly hard to provide any specific meaningful suggestions here. While
you are familiar with your business model regarding the orders, customers,
languages, symbols, revisions etc., others in this newsgroup have no clue on
what they are or how they are related. You did provide a set of CREATE TABLE
statements without any keys, constraints, references etc. however databases
cannot be designed based on such. Esp. when only a couple of lines of
narrative are provided, there is a high chance that the overall business
model and rules are miscommunicated, misrepresented and/or misunderstood.
As a general suggestion, your approach to use GUIDs all over the table as
primary keys with no identifying attribute seems inherently flawed. However,
if you have made provisions for entity identification using UNIQUE NOT NULL
constraints, perhaps you might be able to work it out to some extent.
Consider using temporal datatypes for tracking historical information unless
you are using them already.
Also a few general design rules of thumb, if it helps:
* When you have a one-to-one relationship between two entity types, unless
there are any non-dependency preserving relationships, you may represent
them in a single table.
* When you have a many-to-one relationship between two entity types, you
should use a referential integrity constraint ( FK ) between the tables
representing these entity types
* When you have a many-to-many relationship between two or more entity
types, you should introduce an "association" table which reduces the schema
to two or more many-to-one relationships on each table representing these
entity types.
Anith|||
"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creationof tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
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 ,
[ChangeStamp] timestamp 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 ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp 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 ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp 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 ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Is it now possible that you provide some judge of my record-history idea or
provide some other ideas how to perform this correctly?
Adam|||"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creation of tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
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 ,
[ChangeStamp] timestamp 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 ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp 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 ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp 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 ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Can you now provide some judgement of my record-history idea or provide some
other ideas how could it be performed correctly?
Adam