Red Gate forums :: View topic - Data Generation with Compute columns which function as a FK
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 2
SQL Data Generator 2 forum

Data Generation with Compute columns which function as a FK

Search in SQL Data Generator 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
F.B. ten Kate



Joined: 05 Jul 2013
Posts: 3

PostPosted: Fri Jul 05, 2013 11:03 am    Post subject: Data Generation with Compute columns which function as a FK Reply with quote

We're going to be upgrading our package to the SQL Development Bundle where we're currently only using SQL Compare.

Now i've downloaded the trail to start playing around with some things and one of the things I find very interesting is the Data Generator.

Sadly i'm trying to generate some data on one of our databases and i'm getting no joy at the moment because the way our databases are designed. For example we have the following design for addresses.

Address (base table, which holds an IsPostBoxOffice boolean, defining the type)
PostOfficeBoxAddress (holding only the PO box number)
GeographicalAddress (holding a house number, street)

Now, in order to make sure a PO only references a Address which has IsPostBoxOffice as true, this field combined with the ID is added to a Unique Constraint. This means in the PO table we have a IsPostBoxOffice field which is computed (always 1) and both the ID and IsPostBoxOffice field are referencing the Address table.

This ensures anyrecord entered in the specific subtables (Geographical & PostOfficeBox) will ALWAYS reference a correct record in the Address table.

This design might not be hugely common but it is (by my knowledge) the only way to design this efficiently.

Sadly the generator get's nothing but FK reference exceptions Sad

Naturally I could edit the design and hope no one is dumb enough to make mistakes, but I kinda like having constraints to stop some of the stupidity in the world.

Is there a way to fix this with a custom generator, is this something that might need to be fixed in the Data Generator tool completely? What could I do to make this tool as usefull for us as I believe it could be!
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Mon Jul 08, 2013 10:37 am    Post subject: Reply with quote

Hi,

I think what you need to do is create some data as it was a computed column? I have a question because I don't think you included the whole schema (the ID column, for a start). But what I think you need is to "mimic" a real computed column - ie derive a value using a combination of the ID and IsPostBox, assuming that a value is supplied in the PO Box column, from the generated data.

This is possible only using the Ruby extension from here: ftp://support.red-gate.com/patches/SQL_Data_Generator/SDGDLRGenerator_2.0.3.1.zip

Unfortunately this is not part of the product so there is no "official" support for it except this article the original author wrote:
https://www.simple-talk.com/dotnet/.net-framework/embedding-ironruby-and-the-dlr-into-sql-data-generator/
Back to top
View user's profile Send private message
F.B. ten Kate



Joined: 05 Jul 2013
Posts: 3

PostPosted: Mon Jul 08, 2013 12:34 pm    Post subject: Reply with quote

Hi,

I'm not sure what this Ruby Extension is but i'll look into it. But no, I do not need to create data as it was a computed column.

Basically I've got a table called Addres filled with the following values;

ID IsPostalBox ZIPCode
1 1 123456
2 1 234567
3 0 123456
4 0 234567

I'd like the following tables called PostBoxOfficeAddres & GeographicalAddress to be filled with the values in the Address table which mean that in the PostBoxOffice Address i'd expect;

ID IsPostalBox PostBoxNumber
1 1 5000
2 1 5001

And geographical;

ID IsPostalBox HouseNumber
3 0 5
4 0 6

I've left some columns out of this picture but I hope it explains what I'm trying to generate.

In the two later table (Postbox & Geographical) the IsPostBox is a computed column, it doesn't actually compute anything, it's a fixed value of 0 or 1. It allows for a FK relationship between the two "sub tables" and the "base table" and it ensures value 1 1 123456 can never be referenced in the Geographical Table.

Also, since it's the FK i cannot change the generator, as it's fixed to "Foreign Key Generator" which posses a problem aswell.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Wed Jul 10, 2013 11:04 am    Post subject: Reply with quote

Quote:
Also, since it's the FK i cannot change the generator, as it's fixed to "Foreign Key Generator" which posses a problem aswell.


yes, if SDG won't let you change the type of generator, that may be a show-stopper for you. But presumably this just means the data in the other side of the relationship should be consistent. Still not sure how to solve this, based on limited information.
Back to top
View user's profile Send private message
F.B. ten Kate



Joined: 05 Jul 2013
Posts: 3

PostPosted: Thu Jul 11, 2013 7:30 am    Post subject: Re: Reply with quote

Brian Donahue wrote:
Still not sure how to solve this, based on limited information.


Well what information would you like?

I've written the relationship, I could also update the actual create scripts from these tables if that helps? I don't think it'll be fixable in the current version of SQL Data Generator, if anything it might be something that get's added in a future version of SQL Data Generator as I simply don't think that the current generator is able to do this specific job.

If the create script or anything else would help, just ask for specific information and I'll see what I can do.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Mon Jul 15, 2013 9:52 am    Post subject: Reply with quote

I'd probably need the whole schema and some consultancy time to work on this. But I figured if you cannot change the FK generator and you need to do that, it's probably going to be a show-stopper anyway.

We support people can do workarounds but if the operational capability needs to be changed, all we can do is suggest changes.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Wed Jul 17, 2013 12:17 pm    Post subject: Reply with quote

I tried to create a schema based on this but I can't do the computed column spec. AFAIK a computed column is derived from values in the same table but you are saying IsPostalBox is derived from the Address table?

If I was designing the database, I would make the PostOfficeBoxAddress and GeographicalAddress views on the address table. That would save data duplication and allow you to compute the value of IsPostalBox.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group