Hi

I am trying to design an article website and have got a mental block over the best way to design the database, I have attached a screenshot of the current design (please ignore the column types), I will try to best explain the problem below, please let me know if you need any additional information

An article can be in any number of categories (article_to_category table) all the articles have set values which are stored in the article_content. Depending on the type of the article it can have additional attributes which are stored in the report, article, event, viewpoint tables.

The problem is there is not direct link between the article_content table and the attributes in the above tables, I would have to union all the tables together and workout which of the values I need to display.

I have looked at a EAV model and feel it's not the correct way to go. I have thought about combining the report, article, event, viewpoint tables into the article_content and making the column NULL but this feels 'wrong'.

Thanks for your advice, please let me know if you need any further explanation

Regards

there is, actually -- article_content_id in each of those tables is a foreign key back to the article_content table

but a UNION isn't right, you'd want to use LEFT OUTER JOINs instead, one for each of the subsidiary tables, which will work nicely because only one of the joins will return anything for each given article

and you are right

that's actually quite workable, although you should stick to the separate subtables for the time being

Hi

thanks for the reply,

The problem is if I select an article direct for the article_content table I don't know which of the report, article, event, viewpoint tables to join it to.

ummm, are you saying if I do a LEFT OUTER JOIN on all the tables that it will find a match and just return the columns from the table with the correct article_content_id?

the phase 'time being' worries me can you see any pitfalls in the current way I have designed it, really don't want to have to change the structure in 3 months time.

Again thanks for you help!!

yes, multiple LEFT OUTER JOINs, as only one of them will return anything

for the time being means that you're on the right track, and the only thing that will change your mind to a single monolithic article_content table with lots of nulls in each row is if you get tired of coding all your application scripts to handle the different subtype tables

Thanks for the advice really appreciate it been agreeing and disagreeing with myself for days over this - good to get someone else knowledge and opinion.

Cheers