Show stopper

[transparent]

I've 4 months into developing my website.

All was going well... but I've hit a brick wall.

I have different types of content, all of which is quite different. The data for each type is stored in its own table..

i.e. artist table, gig table, music table.

My site allows this content to be reviewed.

So I have a review table that looks like this:

ID Title Body TargetType TargetTypeID

1 A title text Artist 2
2 A title text Gig 1
3 A title text music 3

I want to create a stored proceedure that returns the review data + gets additional data from associated Types table.

For example row 1 will need to look in the Artists table for the extra info.

Row 2 will have to look in the gig table.

In all cases the additional information will be of the same format i.e. a location column.


In the review table the TargetType column can be used to determine which table to look in... however I have no idea how to implement the stored proceedure to do this.

Any help would be appreciated!!!

 

[ArtieChoke]

select * from review, artist
where TargetType = 'artist'
union
select * from review, gig
where TargetType = 'gig'
union
select * from review, music
where TargetType = 'music'

is one way to do it.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[ArtieChoke]

doh! you'll need to add other join info:

select * from review, artist
where TargetType = 'artist'
and review.xxx = artist.xxx

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[transparent]

Cheer!!

I'm considering moving the locationID to the review table.

The data will no longer be normalised - but it will probably be faster???

What would you do? Keep the data normalised and take the approach you suggested, or move the locationID??

 

[ArtieChoke]

Space is cheap. If it's strictly for reporting, shouldn't be a big deal. In fact, in data warehouse, the data is designed for analysis and is so unnormalized, at first it makes your head spin until you get used to it!

On the other hand, if you had a generic location table, you could store the id in the review table...

"I think we're all Bozos on this bus!" - Firesign Theatre