SQL Query, or perhaps DB Design question

Databases
1

It’s been a good while since my DB class in college. I may have a DB Design / Normalization issue, or I may just not remember how to write the correct query.

(I’ve removed some fields for simplicity.)

Concept: Think of it as a library. Items exist (books, dvds, etc.). There may be multiple instances of an item (say a book). Two instances may be of the same item but in different languages.


ITEM table:
item_id (PK), item_type_id (FK to ITEM_TYPE), author_id (FK to AUTHOR), ...

ITEM_INSTANCE table:
item_instance_id (PK), item_id (FK to ITEM), lang_id (FK to LANG), status_id (FK to STATUS), ...

ITEM_TITLE_LANG table:
item_id (joint-PK, FK to ITEM), lang_id (joint-PK, FK to LANG), title

LANG table:
lang_id (PK), lang_code, lang_description

AUTHOR table:
author_id (PK), author_name, ...

I’d like to perform a search and search both titles and author_names in the same query.

My desired result is a single row for each ITEM_INSTANCE that has a matching ITEM.

The query I wrote looks something like this (let’s say the search query is EL)



SELECT i.item_id, a.author_name, ii.item_instance_id, itl.title, lng.lang_description
FROM ITEM i
  JOIN AUTHOR a ON (i.author_id = a.author_id)
  JOIN ITEM_INSTANCE ii ON (i.item_id = ii.item_id)
  JOIN ITEM_TITLE_LANG itl ON (i.item_id = itl.item_id)
  JOIN LANG lng ON (lng.lang_id = itl.lang_id)
WHERE UPPER(itl.title) LIKE '%EL%'
  OR UPPER(a.author_name) LIKE '%EL%';

But due to two joins from ITEM (one to ITEM_INSTANCE and the other to ITEM_LANG), I’m getting one row for each ITEM_INSTANCE / LANG combination.

Behaviors of this query: let’s say I have one copy of The Hobbit in English and one in Spanish. Then the above query will get me this:


item_id, author_name, ii.item_instance_id, itl.title, lng.lang_description
1, J.R.R. Tolkien, 1, The Hobbit, English
1, J.R.R. Tolkien, 2, The Hobbit, English
1, J.R.R. Tolkien, 1, El hobbit, Spanish
1, J.R.R. Tolkien, 2, El hobbit, Spanish

It’s not keeping straight which one is English and which is Spanish.

I tried modifying a line of my query as follows


  JOIN ITEM_TITLE_LANG itl ON (i.item_id = itl.item_id AND ii.lang_id = itl.lang_id)

but this eliminates the desired ITEM_INSTANCE record that was not a match directly on the title, but still is related to the same ITEM.

Note: If the search query is a match on the author_name (e.g. ‘%TOL%’ for Tolkien) or if the search query is a match for the title in both languages (e.g. ‘%HOB%’), then it returns the rows as desired.

I hope I have included sufficient information without being too long-winded. Thank you in advance for any and all help!!

2

could you expand on this a bit?

it appears that an item instance matches an item based on the item_id only – i don’t understand the “was not a match directly on the title” part

3

Thank you for your response! I shall try to clarify.

Let’s say I have one ITEM entry which represents the book The Hobbit.

And let’s say I have two copies, one in English and one in Spanish. These would be two ITEM_INSTANCE entries that are both tied to ITEM, each with a different lang_id for their respective languages.

The title in English is ‘The Hobbit’ and the title in Spanish is ‘El hobbit’. These two title entries are held in the ITEM_TITLE_LANG table and are associated with the ITEM (item_id) and the LANG (lang_id).

So the join to get the title stored in ITEM_TITLE_LANG can be joined on both the item_id from ITEM and the lang_id from ITEM_INSTANCE.

The reason that doesn’t work is because of the results I want back. I don’t want just the matching row from ITEM_INSTANCE. I want every row from ITEM_INSTANCE that has an ITEM that has an ITEM_INSTANCE or AUTHOR with a match.

Boy that’s a confusing sentence. Not sure if it makes sense.

In the UI, I’d like to display something like this:


The Hobbit | Tolkien, J.R.R. | Book
 -- The Hobbit, Available
 -- El hobbit, Checked Out

even if the search term ‘El’ only matches the title of one of the ITEM_INSTANCE titles found ITEM_TITLE_LANG.

Does that make more sense hopefully?

4

about the search… not really :slight_smile:

but about the tables, yes, it helps

The title in English is ‘The Hobbit’ and the title in Spanish is ‘El hobbit’. These two title entries are held in the ITEM_TITLE_LANG table and are associated with the ITEM (item_id) and the LANG (lang_id).

there’s the problem

the title entries should not be related to the item, but rather to the item instance

i’ll bet your search works after you fix that

:slight_smile:

5

Thank you Rudy, I’ve thought/wondered about that. Doing so would lead to data duplication which, correct me if I’m wrong, is a sign of poor database design / not-normalized data, right?

Here’s how it would lead to data duplication:

If the ITEM_TITLE_LANG is changed to ITEM_INSTANCE_TITLE_LANG, then, well, you wouldn’t even need the table anymore – you’d just store the title in the ITEM_INSTANCE table in whatever language it is. Then, if you have multiple copies of The Hobbit in Spanish, you’ll have multiple entries in ITEM_INSTANCE with the same ‘El hobbit’ title.

It’s not the worst thing in the world, I realize, but, I dunno, it just doesn’t fit right with me.

In more clear English:
A book has a title. A copy of a book may be in any language, but the title of the book, whatever language it is in, refers to the book itself, not a specific copy of the book.

Perhaps it’s not worth making the distinction in the data model… not sure.

6

your call, but i would rather fix the data model so that your query doesn’t produce cross joins of instances and langs on each item

7

Figured it out!!

I needed to do a sub query to do it, and my join with ITEM_TITLE_LANG needed the join on both item_id and lang_id, and voila! :slight_smile:

Thank you for your time and assistance!


SELECT i.item_id, a.author_name, itl.title as lang_title, lng.lang_description
FROM ITEM i
  JOIN ITEM_INSTANCE ii
    ON (i.item_id = ii.item_id)
  JOIN ITEM_TITLE_LANG itl
    ON (itl.item_id = ii.item_id AND itl.lang_id = ii.lang_id)
  JOIN AUTHOR a
    ON (i.author_id = a.author_id)
  JOIN LANG lng ON (lng.lang_id = itl.lang_id)
WHERE i.item_id IN
  (SELECT i.item_id
    FROM ITEM i
     JOIN ITEM_TITLE_LANG itl
       ON (i.item_id = itl.item_id)
     JOIN AUTHOR a
       ON (i.author_id = a.author_id)
    WHERE UPPER(itl.title) LIKE '%EL%' OR UPPER(a.author_name) LIKE '%EL%');
8

wow

:slight_smile: