Generic links - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Generic links

Author

Topic

peteh
Starting Member

8 Posts

Posted - 2011-05-18 : 20:49:42

Lets assume I have tables A, B and C. I now want to create a new Table D which can hold a reference to a record in A, B or C.

Bear in mind that A, B and C may all have different types of primary keys, some may be compound keys and others may contain only an IDENTITY column. I'm not worried about referential integrity (i.e. it doesn't matter if an A dies which has a D pointing to it).

Has anybody any good pointers how this could be accomplished nicely?

Thanks in advance for your input!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 22:50:47

quote:
Bear in mind that A, B and C may all have different types of primary keys, some may be compound keys and others may contain only an IDENTITY column...Has anybody any good pointers how this could be accomplished nicely?

It can't. You can replicate the key columns in D, but for each related row, you could only populate one table's columns in there and leave the other columns NULL. And unless there are additional columns in D it's rather pointless.

Do you have an actual structure already? Can you post it? What's the purpose of having 3 different tables with a 4th for "links"?

peteh
Starting Member

8 Posts

Posted - 2011-05-19 : 19:17:12

The system I work with deals with load building (pallets and shipments) and is highly OO. Unfortunately, OO and RDBMS is a rocky marriage.

When the system receives input from external agents, we patch data that is wrong, conflicting or has details missing instead of rejecting it outright.

Our patching is not bullet proof. To fix the problem correctly, users may need to take extra steps. For example, if we don't know the case dimensions of a product, we calculate something based on other values and generate an error message. These substitute values may be totally out of whack rendering the entire load incorrect. The remedial action is for users to fix the incorrect data.

As you can imagine, incorrect product data is just one problem source, data may also be incorrect for tractors, trailers, pallets, order items, etc.

We've implement an IssueType table which defines the type of issue and what it applies to (say an issue for a Product, Tractor, etc.) and each type has a severity level. We also added an Issue table which has a foreign key pointing to the IssueType. The Issue table has a pointer to the offending record in the form of a JSON string expressing primary key column values (like say "[123,"xyz",false]").

CREATE TABLE [dbo].[IssueType](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [IssueTypeName] [varchar](64) NOT NULL,
  [IssueTypeProsaName] [varchar](64) NOT NULL,
  [IssueDescription] [text] NOT NULL,
  [IssueSeverity] [int] NOT NULL,
  [TableName] [varchar](64) NOT NULL
)

CREATE TABLE [dbo].[Issue](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [IssueTypeID] [int] NOT NULL,
  [JSONKey] [varchar](256) NOT NULL
)

Modules which discover problems can create an appropriate entry in the Issue table. It is common that the same problem is reported often because the faulty object is in use widely and the system needs to guide users to address such issues first.

We built a special interface for users to deal with issues which works like this:

provide a list of issues grouped by issue type and object key sorted by:
- IssueType Severity (descending)
- Number of Issues of that type/object (descending)
provide a popup so the offending object can be corrected in-place
once user has dealt with an issue, we delete all instances in the Issue table for that type/object

A user with a bit of time on his hand to deal with problems is likely to address the most significant problem first. Since it is easy to get to the offending object, there is a better chance users deal with issues.

So what's the problem?

Unfortunately, the system also uses row level security in some tables. It means that some users do not have permission to access certain objects (like say a user does not have access to the product group Explosives nor any products belonging to that group).

Here is an example of how I might provide the list (the WHERE clause content depends on user choices):


SELECT it.IssueSeverity, i.Occurrences, i.JSONKey, it.IssueTypeProsaName, it.IssueDescription
  FROM (  SELECT 
            i.JSONKey, 
            Count(*) Occurrences,
            it.ID
          FROM
            Issue i LEFT JOIN IssueType it on i.IssueTypeID=it.ID
          WHERE
            it.TableName='Product' AND
            it.IssueSeverity > 5 AND
            it.ID in (103,205,33,784,15) 
          GROUP BY
            it.id,
            i.JSONKey
        ) i LEFT JOIN
        IssueType it on i.ID=it.ID
  ORDER BY
    it.IssueSeverity desc,
    i.Occurrences desc

This may yield Issue info a user is not able access because of lack of necessary permission. The problem is that I cannot easily join with the Product table and apply row level filtering because the values are stored in a JSON array.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 21:55:47

Could you add the JSON key as a column in the Product table? Maybe as a computed column? It sounds like it's the real key, at least for what you're tracking. I can't say it's an improvement, but it would make the links explicit and let you join. It won't fix the row security though, and you'll either need dynamic SQL or some ungodly junction to join to other tables.

peteh
Starting Member

8 Posts

Posted - 2011-05-19 : 23:53:17

Of course you could say the JSONKey is the real key. We maintain a meta dictionary which stores the database schema and much more so we have to ability in C++, RoR and Javascript to turn such JSON keys into real predicates, but parsing JSON using TSQL sounds rather daunting. Of course, if that part was done, the rest is comparatively simple to achieve using stored procedures with a bit of dynamic SQL.

Though not such a silly idea, it sounds awkward to me to include the JSONKey as a secondary key on all tables we'd like to link with, particularly when inserting records into tables where an IDENTITY column is the primary key:

insert record assigns value to identity column
retrieve value that was assigned
update the inserted records JSONKey

Maybe this could be done in an Insert trigger, but would SELECT @@IDENTITY still yield the correct value after the trigger was processed?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 07:43:35

There are numerous use cases for secondary/candidate keys, it's not uncommon.

Do you have the JSON key at time of insert? Or is it computed from other data that's inserted? If you have it at time of insert then you don't need a trigger to update it. And if its component column(s) are in the table anyway, use a computed column to construct it, and save some disk space over storing it.

I wasn't suggesting parsing JSON in T-SQL, that is definitely tricky, and also unnecessary. Unless you have JSON keys that are over 900 bytes, you'll have no problem indexing and joining them.

peteh
Starting Member

8 Posts

Posted - 2011-05-20 : 22:30:25

Computing the JSONKey for a given record might be far simpler than trying to store it. However, this would require the examination of meta data. All I know is the table name and that the column values in my JSON array match the values of the primary key columns.

For example, from my Issue record and related IssueType record I can deduce these bits:

Table Name: Product
JSONKey:    ["1", "C9819A00"]

My Product table has these columns in the primary key:

[itmcls] [varchar](1) NOT NULL
[itmcod] [varchar](8) NOT NULL

To get the Product record my Issue record refers to, I could use this query:

SELECT * FROM Product WHERE itmcls='1' AND itmcod='C9819A00'

Though it will be a challenge, I might just be clever enough to write a custom TSQL function to turn multiple column values into a stringified JSON Array such that I could do it this way:

SELECT * FROM Product WHERE AsJSONArray(itmcls, itmcod)=Issue.JSONKey

By doing a by of digging in the schema I can surely workout the key columns (itmcod and itmcls in this example) in the correct order. I could add row level security predicates to this:

SELECT * FROM Product WHERE AsJSONArray(itmcls, itmcod)=Issue.JSONKey AND ProductGroupID NOT IN (SELECT ID FROM ProductGroup WHERE Name IN ('Explosives', 'Poison'))

I suppose I could do this in a stored procedure and process each record from my original query, build dynamic SQL along the way my example does it, and evaluate it. If I don't find a row, I won't return the record.

I would somehow have to pass in a row level filter predicate for each table name in my IssueType table...

Time to look for a real job?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 22:51:08

I've seen (and done) crazier things. If you want decent performance though, I suggest making the JSON key a column, even if it's computed, because it can then be indexed. Generating it at runtime from the function won't permit that and you'll get row-by-row table scans.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources