Database Design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Database Design

Author

Topic

gumby
Starting Member

2 Posts

Posted - 2010-01-27 : 00:11:01

Hi,

I have the situation where there is textual data provided by a governing body which is in a tree like structure.

So I created a table which contains hierarchyID and a few TEXT fields that represent the data sections.

The actual problem I have is that alot of companies reference this data at any point in the tree and are able to modify it but still keep a link to the original.

A possible tree would be

1. Data from global table
1.1 Data from global table
1.2 Modified by Company
1.2.1 Data from global table

This tree needs to be queried often and the rule would be if they have modified it show theirs otherwise show the original

So I guess Im wondering how I can structure this neatly.

Many thanks
Jeremy

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-01-27 : 11:00:11

Could you expand on this requirement:

quote:
the rule would be if they have modified it show theirs otherwise show the original

So if 5 different people modified the same (original) value you need 6 versions? (one original and 5 user modifications)

What version of Sql Server are you using because I wouldn't go with TEXT as your datatype. That is being deprecated in favor of varchar(max) or nvarchar(max).

Be One with the Optimizer
TG

gumby
Starting Member

2 Posts

Posted - 2010-01-27 : 21:03:53

quote:
Originally posted by TG

So if 5 different people modified the same (original) value you need 6 versions? (one original and 5 user modifications)

Correct the 5 user modifications are essentially separate companies and have no idea about any other modifications. (ie can only see original and their own changes)

quote:
Originally posted by TG
What version of Sql Server are you using because I wouldn't go with TEXT as your datatype. That is being deprecated in favor of varchar(max) or nvarchar(max).

SQL2008, did not realize the TEXT datatype was being depreciated. Basically fields store RichText.

Cheers
JM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-01-28 : 13:35:26

Perhaps you add a [CompanyID] column to the table and include it as part of the Primary Key with your HierarchID. All Global rows will be in the table with a CompanyID of say "0". Then for each company modification add another row for that hierarchy. Like this: try the different @companyID values to see the results:


declare @yourTable table 
       (hierarchy varchar(10)
       ,companyid int
       ,Val varchar(100)
       ,primary key (hierarchy, companyid)
       )

insert @yourTable
          select '1.', 0, 'Data from global table'
union all select '1.1', 0, 'Data from global table'
union all select '1.2', 1, 'Modified by Company 1'
union all select '1.2', 2, 'Modified by Company 2'
union all select '1.2', 0, 'Data from global table'
union all select '1.2.1', 0, 'Data from global table'

declare @companyid int
set @companyid = 1
--set @companyid = 2
--set @companyid = 3

select hierarchy
       ,val
from   (
       select hierarchy
              ,Val
              ,seq = row_number() over (partition by hierarchy 
                                        order by 
                                          case 
                                          when @companyid = companyid then -1 
                                          else companyid 
                                          end)
       from   @yourTable
       ) d
where  seq = 1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources