How do you handle complex data structures (OOP) in a mySQL DB?

Question

I'll try to illustrate what I mean with an example.

Say you are running a site that has users and allows posts.

You have a list of users and each user has:
a name
a password
choice of theme
POSTS:
- title of post
- time/date of posting
- post ID
- array of tags for the post

User ID/name/pass/theme is easy. Each variable can be a column, ID auto-incs. When I get to the posts I'm not sure what to do. In nice friendly OOP I would just make a post object type and make an array of those for the user. How am I supposed to do this in the mySQL DB? I was sort of shocked that it wasn't one of the 1st things in my textbook, this has to be pretty damn common. Anyways, I could probably make a horribly ugly hack to get it to work but I'd like to know the 'correct' way.

Thanks!

Answer 1

Members:

id    (autoinc)
name
password
theme_id

Posts:

id    (autoinc)
member_id
title
date

Tags:

id    (autoinc)
name

Tag_Relations:

tag_id
post_id

Posts is your "array" of posts, with the member_id column linking each post to its user. Tags is your "array" of tags, Tag Relations links each tag to one or more posts.

Here is an example of how you could get all posts & tags for a user with one query:

SELECT Members.name, Posts.title, Tag_Relations.item_id, Tags.name 
FROM Members LEFT 
JOIN Posts ON Members.id = Posts.member_id 
LEFT JOIN Tag_Relations ON Tag_Relations.post_id = Posts.id 
LEFT JOIN Tags ON Tags.id = Tag_Relations.tag_id 
WHERE Members.id = 2779;

+----------+-----------------------------------+------------+---------+
| name     | title                             | item_recid | name    |
+----------+-----------------------------------+------------+---------+
| Mike     | One Post's Title                  |        973 | Houses! | 
| Mike     | One Post's Title                  |        973 | Cars    | 
| Mike     | One Post's Title                  |        973 | Hats    | 
| Mike     | Another Post's Title              |        973 | Cars    | 
| Mike     | Yet another post                  |        975 | Homes   | 
| Mike     | Guess what?!                      |        976 | Houses! | 
| Mike     | Another one :)                    |        977 | Noses   | 
| Mike     | Another one :)                    |        977 | Mouth   | 
| Mike     | Another one :)                    |        977 | Head    | 
| Mike     | Another one :)                    |        977 | Knees   | 
+----------+-----------------------------------+------------+---------+

Answer 2

Learn about normal forms (several good tutorials online including this one). Database engines are extremely efficient in doing JOIN operations between flat tables that have been indexed appropriately.

The basic idea is that you identify the entities in your database (e.g. the users/posts/themes you mentioned), and the relations between them (one-to-one, one-to-many, or many-to-many). This allows you to split your data up into flat tables which can be efficiently reassembled.