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

Question

I will try to illustrate what I mean with the example.

Suppose you run a site that has users and messages are allowed.

You have a list of users, and each user has:
name
password
topic selection
POSITIONS:
- job title
- time / date of publication
- message ID
- an array of tags for the message

User ID / username / password / theme are simple. Each variable can be a column, an autoincrement identifier. When I get to the posts, I'm not sure what to do. In a nice friendly OOP, I just create a post object type and create an array for the user. How should I do this in mySQL database? I was shocked that this was not one of the first things in my textbook, it should be pretty damn common. In any case, I could make a terribly ugly hack to make it work, but I would like to know the "right" way.

Thanks!

Answer 1

Users

id (autoinc) name password theme_id 

Posts:

 id (autoinc) member_id title date 

Tags:

 id (autoinc) name 

Tag_Relations:

 tag_id post_id 

Messages is your message array, with the member_id column linking each message to its user. Tags - your "array" of tags, tag relationships associate each tag with one or more messages.

Here is an example of how you can get all the messages and tags for a user with a single request:

 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 Title | 973 | Houses! | | Mike | One Post Title | 973 | Cars | | Mike | One Post Title | 973 | Hats | | Mike | Another Post 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 online tutorials, including this one ). Database engines are extremely efficient at performing JOIN operations between flat tables that have been indexed accordingly.

The basic idea is that you identify the entities in your database (for example, the users / posts / topics that you mentioned) and the relationships between them (individual, one-to-many, or many-many). This allows you to split the data into flat tables that can be effectively collected.