MySQL, per found record join a different parent table.

Question

I have the following parent <-> child datamodel:

(almost every line is a table, indented means child-of)

consumerGoods
    food
        meat
            item
        fruit
            item
        vegetable
            item

The child-items of meat, fruit and vegetables are in the same table (named items) because they have identical attributes. In the items table I have fields that describes the parent and the parentId.

So an item record could be:

id:1
parentType:meat
parentId:4
price:3.25
expDate:2009-12-31
description:bacon

I'm now building a full text MySQL search for the contents of the description field in "items", but I also want each result to have the information of its parent table, so a "bacon-item" has the data that's in its parent record. I also want each returned result to have data that is in the parent food record and the parent consumerGoods record.

I've got the following query now, but I don't know how to join based on the value of a field in a record, or if that's even possible.

SELECT * FROM item WHERE MATCH (description AGAINST ('searchKey')

One way to do this is is to do multiple queries for each matching "item" record, but if I had a lot of results that would be a lot of queries and would also slow down any filtering I'd want to do for facet-based searching. Another option is to make a new table that contains all the parent item info for each item record and search through that, but then I'd have to constantly update that table if I add item records, which is redundant and quite some work.

I'd like to hear it if I'm thinking in the right direction, or if I'm totally misguided. Any suggestions welcome.

Answer 1

You could join against a subquery containing the union of all parent types:

select *
from item
left join (
    select 'meat' as type, Redness, '' as Ripeness from meat
    union all
    select 'fruit' as type, -1 as Redness, Ripeness from fruit
    union all
    select 'vegetable' as type, -1 as Redness, Ripeness from vegetable
) parent on parent.type = item.parentType

But if you can, redesign the database. Instead of the complex model, change it to one table of Items and one table of Categories. The categories should contain one row for meat, one for fruit, and one for vegetables.

Answer 2

As a general rule of thumb your database structure should contain data, but should not itself be data. A sign that you're breaking this is when you feel that you have to join to a different table based on the data you're reading from some other table. At that point you need to back up and consider your overall data model because odds are very good that you're doing something not quite right.

Answer 3

As @Andomar suggested, the design is a bit off; having "multiple parent tables" does not map to DB foreign keys concept. Here is one possible suggestion. This one uses two levels of super-type/subtype relationships. Super-type table contains columns specific to all subtypes (categories), while subtype tables contain columns specific only to the category.

Answer 4

Since your example is contrived, it's difficult to know what the actual information requirements are in your case. Damir's diagram shows you the correct way to model PKs and FKs when you have a super-type sub-type relationships.

This situation is one case of a pattern called "generalization-specialization". Almost any treatment of object modeling will deal with generalization-specialization, although it may use different terminology. However, if you want to find articles that help you build a relational database that uses specialization-generalization, search for "generalization specialization relational modeling".

The best of the articles will start by teaching you the same concept that Damir's response illustrated for you. From there, you will learn how to create queries and views that can search for either all kinds of items, or for particular kinds of items, if you know what you are searching for.

A sample view follows:

create view FruitItems as
select
    c.ConsumerGoodsID,
    Price,
    Description,
    ConsumerGoodType,
    ExpiryDate,
    FoodType,
    IsTropic
from
    ConsumerGoods c
    INNER JOIN Food f on f.ConsumerGoodsID = c.ConsumerGoodsID
    INNER JOIN Fruit fr on fr.ConsumerGoodsID = c.ConsumerGoodsID

Similarly, you could create views for VegetableItems, MeatItems, and HouseSupplyItems, and even one large view, namely Items, that's the union of each of the specialized views.

In the Items view IsTropic would be true for all tropical fruits, false for all non tropical fruits, and null for Meats, Vegetables, and HouseSupplies. I'm not going to show you the entire Item view for a contrived case, but you get the idea. Especially if you read the best of the articles on relational modeling of this pattern.

The Items view might be a little slow, but it could come in handy when you really don't know any better way to search. And if you search for Istropic = True, you'll automatically exclude all the Meats, Vegetables, and HouseSupplies.