Sorry if this belongs in the Coding Forum.

I'm restructuring the mySQL database for my site to separate news, articles and videos into their own tables. The current system has them all under a single "posts" table, but I have extraneous fields for each group ("sources" just for news, "thumbnails" for videos and articles, etc.) that wind up empty two thirds of the time. I've read that it's best to keep tables concise, so that's the reason for the change.

Now, in order to get content from all three tables to show up in the same feed on the front page (ten at a time, sorted by date), I'm using CREATE TEMPORARY TABLE to basically recreate the structure I had to begin with dynamically. I know this can't be ideal: if nothing else, it probably slows down my page (I haven't actually timed it, though).

But since the tables don't line up I can't use UNION. I haven't really experimented with JOIN, but from what I've read I'm pretty sure that it isn't what I'm looking for either.

So, am I stuck using temporary tables? And, if so, should I just go back to my original structure where everything is in a single table and opt for speed over cleanliness?

The tables I have right now are set up something like this:

Table: news
Fields: id, title, url, author, date, body, sources

Table: articles
Fields: id, title, url, author, date, summary, body, thumbnail

Table: videos
Fields: id, title, url, author, date, embed_src, description, thumbnail

JOINs are what you need, provided there is a key field (primary key/foreign key) that links the records in each table (relationship).

The 'id' fields are what I've been using as the keys, but if I use JOIN and two or more tables have a row with the same id value, won't it join those rows into one and screw up my feed by including, for instance, a video and news item in the same iteration of my while loop?

I'm auto-incrementing the tables individually, so every time I post into, say, the "videos" table, it'll increment the id field of just that table and not the other two. Is there a way to have all three tables auto-increment when I post into just one of them so that it'll guarantee each table will have totally unique keys from the others? (That'll also really improve my commenting system.)

I've been using mySQL for four years, but I'm still not down on everything. Here's what phpMyAdmin says about my indexes for each table:

Keyname: PRIMARY
Type: BTREE
Unique: Yes
Packed: No
Field: id
Cardinality: 0
Collation: A

Thanks for the speedy reply, by the way! That's what I love about this forum.

You need to look at your table structures to reduce duplication (normalise)

eg:
the data should be in a single structure with a field identifying the entry type

Authors should be a table. with a link table connecting author IDs to data IDs

Sources should be a table, again with linking table.

etc etc.

Read up on 3NF (Third Normal Form)

Okay, I obviously have no idea what I'm doing. Here's what I've tried:

SELECT features.body, news.body, users.username 
FROM features, news, users 
WHERE news.user_id = users.id || features.user_id = users.id
ORDER BY STR_TO_DATE(features.datetime, '%Y-%m-%d %H:%i:%s'), STR_TO_DATE(news.datetime, '%Y-%m-%d %H:%i:%s') DESC

SELECT features.body, news.body, users.username
FROM (features,  news) LEFT JOIN users ON news.user_id = users.id || features.user_id =  users.id
ORDER BY STR_TO_DATE(features.datetime, '%Y-%m-%d %H:%i:%s'), STR_TO_DATE(news.datetime, '%Y-%m-%d %H:%i:%s') DESC

SELECT * FROM features,  news, users
WHERE news.user_id = users.id || features.user_id = users.id
ORDER BY STR_TO_DATE(features.datetime, '%Y-%m-%d %H:%i:%s'), STR_TO_DATE(news.datetime, '%Y-%m-%d %H:%i:%s') DESC

SELECT * FROM (features, news) LEFT JOIN users ON news.user_id = users.id || features.user_id =  users.id
 ORDER BY STR_TO_DATE(features.datetime, '%Y-%m-%d %H:%i:%s'), STR_TO_DATE(news.datetime, '%Y-%m-%d %H:%i:%s') DESC

And pretty much any other combination I could think of, including replacing the ORs with ANDs (which only eliminated entries from the "features" table where the author didn't also have posts under the "news" table). When I run the loop it'll only display content from whichever entry table appears last in the FROM clause. Sometimes it'll display the same entry twice but with a different author each time.

But, if I'm supposed to have all of my entry tables under the same structure with just a field indicating entry type, why don't I just use UNION or even a single table? Then I'll just use linking tables to join extra information, like sources and thumbnails, to the entries that require them.

The following works just fine:

SELECT * FROM posts LEFT JOIN users ON posts.user_id = users.id