We are planning to create REST API which has main objects as User and Article. Article object has following fields
- Title - one line
- Description - it can expand to few paragraphs
- Image - one or more images about article
- List of categories - categories of article ( starting with one but in future can be more)
- List of tags - tags related to article
- Status - status of article like pending, reviewed, approved etc
Techlogies/Frameworks we are using:
- REST API with JSON payload type for request response
We discussed few plans but wanted to know what would be the best database schema to store Articles in MySQL Database.
- One table each for article, category, tag and then anoter two tables for many-to-many relation between article-category and article-tag
- One table each for article and category(assuming each article will have one category) and store category_id in article table and store tags as comma-sperated values in article table only Any other way we can store these details in database efficiently?
Another doubt we have is how to store images. Maximum it can be upto 3-4 images per article.
Shall we create another table for images and store as CLOB/BLOB and then in another table store the relation between article and image ?
Or instead of CLOB/BLOB, we store it as base64 encrypted string in database and decrypt on client side ?
Or we upload the actual image to some file hosting solution (e.g. AWS S3..?) and then store the image URL in table ?
Which of the above will be efficient solution for storing and reteriving images.