Have Geek Brain

Restoring types through the Iza relation

My database has 7 tables, one is parent (feed) and the other 3 are children (public feeds, private feeds, generated feeds) of the isA relationship. The channel table has a "Subscriptions" table with a foreign key. Each user can subscribe to any type of channel. The problem is that the view is different for each feed type, which means that I need to create different links for each subscribed feed. Within the current schema, I need to make 3 requests to get the feed type from the feed id. Is there a better solution to this problem?

0


a source to share


1 answer


You can use a view to pre-attach children to their parents and get a consistent result regardless of the type of the child. For instance:

create view feed_links as
select f.feed_name
,      case f.feed_type
            when 'public' then pub.x + pub.y
            when 'private' then pri.z
            when 'generated' then gen.v + gen.w
            end as link
from   feeds f
left outer join public_feeds pub on pub.feed_id = f.feed_id
left outer join private_feeds pri on pri.feed_id = f.feed_id
left outer join generated_feeds gen on gen.feed_id = f.feed_id
where ...;


Or, if your feeds table doesn't have a feed_type column (or equivalent):

create view feed_links as
select f.feed_name
,      case when pub.feed_id is not null then pub.x + pub.y
            when pri.feed_id is not null then pri.z
            when gen.feed_id is not null then gen.v + gen.w
            end as link
from   feeds f
left outer join public_feeds pub on pub.feed_id = f.feed_id
left outer join private_feeds pri on pri.feed_id = f.feed_id
left outer join generated_feeds gen on gen.feed_id = f.feed_id
where ...;
0


a source






More articles:


All Articles