Nabin Nepal

Is there any way to generate data when there is dependency between two columns in a table. For example if a table contains EmployeeID and ManagerID fields and the ManagerID should be one of the EmployeeID generated before or it can be null.

Is there any way to achieve this



Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Henry Zhang - MSFT

For you specific scenario, you can define ManagerID as a foreignkey referencing EmployeeID if EmployeeID is a Primary Key.

Or you can use Data Bound Generator on ManagerID column to pull data from EmployeeID.

The following forum thread also gives you a more complicated scenario and solution in case you have more sophisticated scenario.

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=837864&SiteID=1&mode=1

Please let me know if they work for you.

Thanks,






Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Jeff Welton MSFT

If ManagerID has a foreign key relationship with EmployeeID, then this will just work automatically. However, in cases where you have an informal (not normalized) dependency for one column on another, there is a straight-forward path to success here:

  • If you use the same seed value for the generators for both EmployeeID and ManagerID (and the generators used are the same type), you will get the same sequence of values for both columns. Now, you wish ManagerID to have some NULL values, as well. No problem, just set the Percentage Null property of the generator being used for ManagerID to, say, 50%. The non-null values for ManagerID will still be produced in the same sequence as those produced for EmployeeID. Now, this approach would not work if you needed to generate more non-null values for ManagerID than for EmployeeID, but that seems unlikely in the scenario you describe

There are a number of other possibilities, ranging from the use of the DataBoundGenerator pointing to a shared data source on up to doing some rather involved extensibility work to create a new, custom generator. For many cases, though, use of generator seeding can give you the reproducible values you need from one column to the next.






Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Nabin Nepal

Thanks Jeff

I was just asking as an example. The table that I am trying to store is of tree structure. So creating table for each parent doesnot make sense. So I don't think it has anything to do with normalization. I like your idea of using the same seed. But if the both ids start with non-null values, then in that case id and the parentid will have the same value. That will not help my cause. Do you have a better suggestion

Thanks for the help.





Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Henry Zhang - MSFT

Is it a pure tree or is it a graph that does not allow parent to be itself

Thanks,






Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Nabin Nepal

Its not a graph. Its a pure tree. A node can not point to itself. Is there any help

Thanks

Nabin





Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Henry Zhang - MSFT

Here is the specific solution using Data Bound Generator:

Assume the table is like Employees [EmployeeID, ManagerID, Name, Title].

(1) Create a Database Project

(2) Import the Database with table Employees

(3) Add a Data Generation Plan,

(4) On Column Details window of Employees table, check EmployeeID column and uncheck ManagerID column

(5) Populate data with Yes on delete data dialog. Only EmployeeID column is populated.

(6) If EmployeeID column is a not allow nulls column, you have to make it an allow NULL collumn for the time being.

(7) On Column Details Window of Employees table, uncheck EmployeeID column and check ManagerID column.

(8) select Data Bound Generator for ManagerID column

(9) On the properties window of ManagerID column, set the Connection Infomation property to the current database

(10) Set the Percentage Null to 20 for example.

(11) Set the Select Query property with the following query: Select EmployeeID from Employees

(12) Populate data with Yes on delete data dialog. Now ManagerID column should be popuated with data you want.

(13) restore the Not allow null and other state on EmployeeID column to the original state.

Please let me know if it works for you.

Thanks,






Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Nabin Nepal

Thanks Henry. That worked for me. The solution given by Jeff was also a handy one which allowed me to get repetitive data with the same seed. With a reasonable percentNull value I can have the first record null and other records will not have that problem.

Thanks to both of you.





Re: Visual Studio Team System - Database Professionals Data Generation for Column Dependency

Henry Zhang - MSFT

If there are foreign key relationship from EmployeeID to ManagerID, yes it will work automatically. In both solutions, if you keep the seeds on columns unchanged, you will get the same result each time you populate the data.

Thanks,