Pingo, here is a different take on your question regarding why you have the same run of numbers for consumers and corporate clients.
There are pot-loads of normalization rules and some of them can be stated technically to such a complex level as to obscure the meaning of the rule. So..., here is the MEANING of the rule about keys.
Normalization is about filtering and isolating the contents of a table such that every item you see in the table depends EXACTLY AND ONLY on the prime key. That allows the key to be a stand-in for the entire record by proxy (and by lookup or join, in practical terms). That is the "real" meaning of having a relationship between two tables.
The problem here is defining which is the "one" side and which is the "many" side. The design you have shown us is somehow reversed. Since the only thing in your parent table (as you described it) is a number and a class-code (consumer/corporate), I wonder why you even need this information?
Let's take a look at what I mean by that. In the tblConsumer and tblCorporate stuff, you have the key number as a foreign key. But will this information be a one-to-many case? It superficially looks like it would be one-to-one as the way you described it. In that case, all you would use that PK number for is to look up the class code. But since you knew already (based on which table it was in) that this was a consumer or corporate client, you have everything you need already in the individual tables.
So the question then becomes, WHY did you want to co-mingle these values?
Here is my thought, and perhaps this will open your eyes to a different viewpoint. Your data set seems like the parts have almost totally independent origins. Is there a need for a unique number from either table to be honored by the other table? (This is a question YOU must answer, based on your knowledge of the program.)
How often do those tables get treated separately? How often must they come together? If they are mostly separate but you have one or two reports or VERY simple queries that work on the lowest common subset of fields that exist in both of the original tables, consider a UNION query as a way to merge the tables when they need to be merged. Otherwise, let them be independent.
If I have missed your intent here, sorry. It was not my desire to confuse you. However, your problem description does not clarify the need for joining these apparently disparate data subsets. Which is why Neil and I seem skeptical.
