How to handle multiple one->many relationships?

Question

I have a database with several tables, 5 of which are dedicated to specific publication types. Each of these 5 have a one->many relationship with a status table and a people table. All of these tables are tied together using a unique "pubid". I have a view which includes the pubid (for all 5 types), along with their associated keywords. When a user does a keyword search and the results span across more than 1 of those 5 publication type tables I am really not sure how to handle it.

If there was only a single publication type (so just one table that had a 1->many) it would be very easy to accomplish with a nested join, something like:

SELECT * FROM articles 
  INNER JOIN status ON articles.spubid = status.spubid 
  INNER JOIN people ON articles.spubid = people.spubid 
  WHERE people.saffil = 'ABC' ORDER BY people.iorder, articles.spubid;

In that example 'articles' is one of the 5 tables I mentioned that has a 1->many relationship. Lets say that the keyword search brings back results that include articles, books and papers. How can I achieve this same end with that many different tables? If I were to figure out how to use a JOIN in that case the Cartesian product would be so large that I think the overhead to parse it out into a usable format would be too high. What are my other options in this case?

Answer 1

Perhaps you could create views for each of the 5 types (vwBook, vwArticle, etc.)

As you're searching, perhaps call into a stored proc that will use all 5 views using the keywords that you throw at it. Each of the 5 results could go into a table variable in your stored proc.

Modify, of course, as you see fit. Here's a broad stroke example:

create proc MySearch

@MySearchTerm varchar(50)

AS
    DECLARE @SearchResultsTABLE
    (
     Type      varchar(10) -- the view you found the result in.
    ,ID        int -- the primary key of the Book record. whatever you want to link it back to the original
    ,FoundText varchar(512)
    --etc
    )

    INSERT INTO @SearchResults(Type, ID, FoundText)
      SELECT 'Articles', ID, SomeKeyField
      FROM vwArticle
      WHERE SomeKeyField LIKE '%' + @MySearchTerm + '%'

    INSERT INTO @SearchResults(Type, ID, FoundText)
      SELECT 'Book', ID, SomeKeyField
      FROM vwBook
      WHERE SomeKeyField LIKE '%' + @MySearchTerm + '%'

    --repeat as needed with the 3 other views that you'd build

    SELECT * FROM @SearchResults

Answer 2

Why are they in separate tables? Are the columns that different? And what columns do you want to return (never ever use select * in production especially with joins), are they different between the different types in your query?

If you can get the columns to be the same I suggest you use UNION ALL. Even if the columns are different by supplying all that you need in each part of the union statement (giving the value of null for those columns that set of tables doesn't have), you can still get what you want. Simplified code follows:

SELECT articlename, articlestatus, author, ISBN_Number 
FROM articles 
  INNER JOIN status ON articles.spubid = status.spubid   
  INNER JOIN people ON articles.spubid = people.spubid   
WHERE people.saffil = 'ABC' 
UNION ALL 
SELECT papername, paperstatus, author, null
FROM papers 
  INNER JOIN status ON papers.spubid = status.spubid   
  INNER JOIN people ON papers.spubid = people.spubid   
WHERE people.saffil = 'ABC'

Answer 3

You could create a view that was the union of all the various tables. The tricky bit is to make sure that all the queries being UNIONed have the same fields, so you'll need to stub a few in each one. Here's a simplified example with only two tables:

CREATE VIEW AllTables AS
  SELECT Afield1, Afield2, NULL as Bfield1, NULL as Bfield2 FROM Atable
    UNION
  SELECT NULL as Afield1, NULL as Afield2, Bfield1, Bfield2 FROM Btable;

You could use something other than NULL as the stub value if necessary, of course. Then you run your query against the view. If you need to vary the formatting according to the publication type, you could include the originating table as part of the view (ie, add "'magazine' AS publication_type" or something similar to each of your selects).

Answer 4

I really don't like the table design here. However I guess redesigning the entire database from scratch is a bit too extreme.

Given the table design, I think you're going to have to go with a UNION and specify the columns in each table. I know it's a monster, but that's what happens when you design tables with lots of columns which are "almost" alike.

And HLGEM is right. Using "select *" in a permanently-stored query is really dangerous.

Answer 5

We ended up creating a very elaborate view which includes the 1-many tables as array columns in the view. This allows for a single query to be performed on a single view and all the required data is returned. The view definition is VERY complex but it is working like a champ, the real trick was using the ARRAY() function in PostgreSQL.