MySQL | Database schema for user posts containing images and files [closed]
In my app, users can have posts which may contain text, headings, images, etc.
What is the proper way of storing these in a database? I can think of two possible ways:
FIRST METHOD
Storing multiple rows of data for ONE post in posts table as follows:
| post_data_id | post_id | data_type | data |
|---|---|---|---|
| 1 | 1 | 1 | Sample Text |
| 2 | 1 | 2 | (Image Link) |
| 3 | 1 | 1 | Another Text |
| 4 | 1 | 3 | (Audio Link) |
where data_type is a foreign key to datatypes table, in which 1 is sample text, 2 is image-link and 3 is the link to audio files.
In this way, for retrieving a single post, I would only SELECT all rows belonging to a specific post_id.
SECOND METHOD
Somehow encoding the post data into a language like HTML as below, and save it into a single row in the posts table:
<Text>Sample Text</Text><Image>Image Link</Image><Text>Another Text</Text><Audio>Audio Link</Audio>
In this way, the user must not be able to post texts which contain these specific tags, and that is exactly what makes me question this method. Is it secure enough to just check whether the text contains the tags or not before sending it to the database?
Looking at the way WordPress manages this; uploads are moved to the uploads directory (usually with a dated folder structure - Example: /wp-content/uploads/2022/01 ). The uploads are then referenced by this path in the databases.
The wp_posts table is used to track these "attachments" (and even store the attachment mime-type to help with processing the attachment file type). These entries are stored with a meta_id (as are all entries in the wp_posts table) to be linked with more detailed metadata from the wp_postmeta table.
I would recommend reading through https://www.wpbeginner.com/beginners-guide/where-does-wordpress-store-images-on-your-site/ to get an understanding of this structure in detail as the structure is tried and tested by WordPress.