1

If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to return the extended row (that joins the securitymaster with the sub table in a UDF or Stored Proc)?

In my example, I will abbreviate the securitymaster table as A, the subtables are B,C,...,Z. Each subtables has its own specific columns. Let's further imagine the A has the first column a1 which is the PK and the last column a10 that holds the value B,...,Z (I.e. a FK to the subtable name). The number of columns in the sub table differ and will normally be specific to each table, however some tables may share the same column names.

If I have a securityID, say @sID, then, I will need to join the table as defined in A.a10 where A.a1 = @sID. I will then need to present select * from F (let's imagine A.a10 was equal to F). I have tried with Execute, but people say its a bad method, also I don't know how to return the value or the combined row from an EXEC within a sp. UDF looks impossible as they can not be dynamic. An example would be really useful as I feel this should be a fundamental tenant of any DB?

Many thanks

Improve this question
0

3 Answers 3

Reset to default
1

Normally, you'd create a view for each subtype. Do the join between the "super" table and the "sub" table in the view.

If you make the views updatable, client code can just use the views.

Improve this answer
0

If i understand your question you can

  1. Construct your query dinamically with syscolumns support and runs it with EXEC.

  2. SELECTs covering all the subtables structures with UNION.

Improve this answer
1
  • Do you have an example? I would appreciate an example where I can then perform select * from myFuntionORProc(@sID ). Can you point in the direction for me to construct the myFuntionORProc example? Many thanks,
    – user5379
    Dec 28, 2011 at 13:30
0

This is not entirely correct design (if I understand correctly what you have). The Foreign Keys constraints should be from a subtype tables, referencing the supertype table. Not the other way around, as your attempt.

So, you need one additional column in every subtype table that holds this specific value (B, C, ... or Z) and is part of the Foreign Key.

See this answer by @gbn, Option 3, for details on how this can done: NULLs in a composite primary key - SQL Server

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.