concat tables with different content

Question

Hi,

I am currently building a small web application for publishing stories using PHP and MySQL. Each story may either be a article (with lots of fields and data), just a hyperlink/url, just an image, or a downloadable file with certain attributes (once again with lots of fields).

My problem is related to finding the optimal database layout for the system. I have thought about 3 different solutions that may or may not work (fields are just examples, don't get caught of with whether the data should be stored in the database or not):

1) Making one table for each type and just UNION them. E.g:

articles
id | date | title | author | preview | last_edited | category | content

hyperlinks
id | date | title | author | url

pictures
id | date | title | author | thumbnail | fullimg

files
id | date | title | author | filetype | filename | filecontent

Is it possible to make a SQL statement that will concat these in such a way that every field is added to the rows from the individual tables and the individual row is "tagged" with its type depending on which table its from? Or do I have to settle for just the common fields (3-4 field WILL be identical for each table) and to individual lookups for each row?

2) Making some sort of index table that references the correct table and row depending on the type?

index
id | type | title | date | referenceid -> either articles, hyperlinks, pictures or files depending on the "type" field.

Is this possible in some way when using foreign keys? I suspect no, but not really sure what you can and can not do in MySQL.

3) Have ALL fields for ALL types in one and the same table and just make the specific field nullable (I dont really like this solution since it appears messy).

Any ideas?

I suspect I can not be the first person to have encountered the issue of references to different entity types/fields depending on some sort of type.

Ideas are highly appreciated.

Answer 1

If you name fields, you can union then quite easily, and throw in whatever constants you like. I do this all the time:

Select id, 'articles' as tablename, title, author, 
    preview, last_edited, category, '' as url
from articles
UNION
Select id, 'hyperlinks' as tablename, title, author, 
    '' as preview, '' as last_edited, '' as category, url
From hyperlinks
...

Answer 2

You have so few fields, just put them all in one table and add a type field. No sense overcomplicating things.

Answer 3

I'd suggest adding a stories table to be the central table. This allows for a great deal of flexibility in the system design and centralizes the data that is core to a story.

Something along these lines:

stories
    id
    date
    title
    author
    article_id
    hyperlink_id
    picture_id
    file_id

articles
    id
    preview
    last_edited
    category
    content

hyperlinks
    id
    url

pictures
    id
    thumb
    full

files
    id
    filetype
    filename
    filecontent

Answer 4

I'd add a 'type' column to each table that specifies what table it came from, and then do an equijoin followed by a group:

SELECT *
FROM articles a INNER JOIN hyperlinks h
    ON a.id =h.id AND a.date=h.date AND a.title=h.title AND a.author=h.author
    JOIN pictures p ON h.id=p.id AND h.date=p.date AND h.title=p.title AND h.author=p.author JOIN files f
    ON p.id=f.id AND p.date=f.date AND p.title=f.title AND p.author=f.author
ORDER BY type;

It's an ugly-looking query but it should do what you want...