Sql-server – How to create a Relation between a table and a many-to-many relation table

I wish I could upvote you 100 points just for thinking about this! I have seen this subject overlooked so many times it's untrue - so well done. From what I understand you actually want to scramble the data within the fields themselves, and although I understand what you are trying to achieve it might not be quite necessary to do so - although it should be considered on a case-by-case basis.

Most data protection laws revolve around the ability to correctly associate a piece of data with an individual - for example a date of birth or a phone number. You can meet the requirements of the law by ensuring that when you move your data out of production into UAT it is jumbled up so it is not easily re-mapped to the original person - especially when you jumble forename and surnames.

However, this does not address the issue for instance of let's say contact details. You can meet the requirements of the law by jumbling the data but the phone numbers are still real, the emails still real etc... they are just not assigned to the correct person. For this I recommend if at all possible clearing that data before passing it into UAT, Red Gate do a piece of software called Data Generator that can create random test data for you so that you can repopulate the fields with data that can be tested against.

As for data scrambling: there exists many applications that do this for you and honestly you are correct in not wanting to reinvent the wheel. The one that we use at our company is a product called Data Masker by a company called Net2000. The license is pretty cheap, it works extremely fast and you don't have to worry about having to disable all your constraints before scrambling the database.

You can of course roll your own solution should you not find anything that meets your requirements - if you do decide to do this I would strongly recommend using CLR procedures to do it as it is much more flexible than pure TSQL (not to say that you can't use TSQL see here).

Once you have chosen an application to perform this for you the next thing you need to decide is what is it you actually want/need to scramble? Honestly your best resource for this is your company legal team and or the company auditors. I know that sometimes we may not like working with them but they will be much nicer to you for approaching them and asking them the question rather than trying to do it on your own and get it wrong, there is absolutely nothing wrong with asking for help - especially when it is as important as this.

I hope this helps you and I wish you good luck in your quest... ;-)

What you are describing is an example of the general situation entity sub-typing.

There are two basic ways to handle entity sub-typing:

One method places common attributes in a table and distinct attributes in separate tables, linked using 1:1 relationships to the common table. Some other considerations:

• The common table may have a partitioning attribute, which is a field that indicates which sub-type of thing each record is.
• The primary key of each sub-type table is a foreign key to the common table.
• You can create views which merge the columns from the common table and each sub-type table, so that for querying purposes you appear to have a "one stop shop" for each sub-type of thing. This can simplify your queries since it leaves you one less join to write out each time.
• This approach makes it easy to apply referential integrity if you have child tables that apply to only a single sub-type (or all sub-types).

Another method is to place all attributes in a single table, and then use declarative constraints and/or triggers and/or application logic to enforce sub-type specific business rules. In this case:

• A partitioning attribute must be used to indicate which sub-type of thing each record is.
• This makes queries easier, but it spreads your business logic out in a way that many people will find confusing and undesirable.
• This approach can lead to many nullable columns, which makes some people itchy.
• This approach doesn't help you if you have child tables that apply to individual sub-types, but it is the only sane approach if you have child tables that apply to multiple, but not all sub-types.

In your case you could pick either approach. Which might be better depends on your preferences and on exactly how many distinct columns there are per sub-type.