-5

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?

Saeed Mohammadi
  • 25
  • 3
  • 3
    The second one is a more fancy looking delimited list, so it violates 1NF and therefore should not be used. See also ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Jan 12 '22 at 12:15

1 Answers1

1

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.

samson4649
  • 81
  • 5
  • How does it know where to put those attachments/metas? Does it put them in order based on the meta_id? – Saeed Mohammadi Jan 12 '22 at 12:35
  • WordPress isn't exactly a role model regarding database design, squashing everything in a meta table regardless of the entity... (Yeah, yeah, I know, it's flexible like that, but if you don't need such flexibility, don't do it like them.) – sticky bit Jan 12 '22 at 12:36
  • 1
    @stickybit - I agree, but it helps to get an understanding of a structure that is widely available. Its not perfect but its a good balance of simplicity across all aspects of managing the site. – samson4649 Jan 12 '22 at 12:44
  • 1
    @samson4649: I have doubts about recommending a beginner to build up their understanding on wrong examples (unless it's clearly marked as "and this is **not** how to do it")... – sticky bit Jan 12 '22 at 12:46
  • Wordpress is a popular example of the inefficient "Entity-Attribute-Value" schema pattern. Further, its wp_postmeta table is not adequately indexed. Even so, it is a reasonable example of how to implement small datasets as described in the Question. – Rick James Jan 12 '22 at 17:09
  • Alas, there is no "perfect" schema for the problem in the Question. EAV (a la WP) and JSON (a la the HTML 'solution' in the Question) are two contenders; each has benefits and drawbacks. There is no viable pattern (in RDBMS) in between. [_Mixing them_](http://mysql.rjweb.org/doc.php/eav) is a compromise. – Rick James Jan 12 '22 at 17:15