Hi guys,
Loooking for view / opinions on the following ( got a debate with someone so thought Id ask everyone here)...
Im going to comapre two ways in which i can see i could version groups of data within a table.
The business need here is that we have forms that have fields which contains values from lookup fields. We are basically creating a table for lookup fields, the requirement is that we should be able to see the easily version these lookups. so when one or more values change we create a new version of our available lookup fields.
Consider the following two tables...
CREATE TABLE [dbo].[Method1](
[TypeID] [int] IDENTITY(1,1) NOT NULL,
[TypeName] [nvarchar](100) NOT NULL,
[Version] [int] NOT NULL,
[Value] [nvarchar](100) NOT NULL
)
CREATE TABLE [dbo].[Method2](
[TypeID] [int] IDENTITY(1,1) NOT NULL,
[TypeName] [nvarchar](100) NOT NULL,
[ValueID] [int] NOT NULL,
[Value] [nvarchar](100) NOT NULL,
[Deleted] [bit] NOT NULL
)
Now in table Method1 data will look like this....
TypeID TypeName Version Value
------- ----------- ---------- --------------------------
----------
-
1 Type 1 1 Some Value 1
2 Type 1 1 Some Value 2
3 Type 2 1 Some Value 1
4 Type 2 1 Some Value 2
To add / remove or even edit values of a given TypeName you create a new version of my TypeName
ie...
TypeID TypeName Version Value
------- ----------- ---------- --------------------------
----------
-
1 Type 1 1 Some Value 1
2 Type 1 1 Some Value 2
5 Type 1 2 Some Value 1
6 Type 1 2 Some Value 2
7 Type 1 2 Some Value 3
3 Type 2 1 Some Value 1
4 Type 2 1 Some Value 2
I kinda see method one as explicitly saying this is my TypeName, i.e. these are its versions and these are its values.
Method2 data looks like this....
TypeID TypeName ValueID Value Deleted
-------- ------------ ---------- --------- ---------
1 Type 1 1 Value 1 0
2 Type 1 2 Value 2 0
3 Type 2 1 Value 1 0
4 Type 2 2 Value 2 0
To add data - create a new row of a given type and new value id
TypeID TypeName ValueID Value Deleted
-------- ------------ ---------- --------- ---------
1 Type 1 1 Value 1 0
2 Type 1 2 Value 2 0
3 Type 2 1 Value 1 0
4 Type 2 2 Value 2 0
5 Type 1 3 Value 3 0
To edit data - create a new row of a given type and reuse the value id
TypeID TypeName ValueID Value Deleted
-------- ------------ ---------- --------- ---------
1 Type 1 1 Value 1 0
2 Type 1 2 Value 2 0
3 Type 2 1 Value 1 0
4 Type 2 2 Value 2 0
5 Type 1 1 Value 1a 0
To remove data - inset the type name again and mark as deleted
TypeID TypeName ValueID Value Deleted
-------- ------------ ---------- --------- ---------
1 Type 1 1 Value 1 0
2 Type 1 2 Value 2 0
3 Type 2 1 Value 1 0
4 Type 2 2 Value 2 0
5 Type 1 1 Value 1 1
Now i see method two (for better or worse at this stage) as kinda implicit versioning in that you having to rely on the developer knowing that the max type id for typname / valueid pair is the most recent value.
As much as i dislike duplicating data I cant help but feel method one is easier to understand and has benefits in making it easier to report on.
What are peoples views on how to keep versions of groups of data in the table (based on these examples or other methods).
Views much appreciated.
Start Free Trial
