-1

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:

  • Java
  • Spring
  • SpringBoot
  • MySQL
  • 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.

  1. One table each for article, category, tag and then anoter two tables for many-to-many relation between article-category and article-tag
  2. 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.

  1. Shall we create another table for images and store as CLOB/BLOB and then in another table store the relation between article and image ?

  2. Or instead of CLOB/BLOB, we store it as base64 encrypted string in database and decrypt on client side ?

  3. 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.

closed as off-topic by Simon B, Jarrod Roberson, JeffO, Robbie Dee, ZJR Apr 9 '18 at 1:48

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions asking for assistance in explaining, writing or debugging code are off-topic here. These can be asked on Stack Overflow if they include the desired behavior, a specific problem or error, and the shortest code necessary to reproduce it in the question (see Minimal, Complete, and Verifiable Example)." – Simon B, JeffO, ZJR
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 'store category_id in article table'. How can you do this? You define it as a 'many to many' relationship. – Carra Mar 7 '18 at 14:15
  • 'tags are comma-sperated values in article table '. That's usually a bad idea. Especially if you have to find all articles linked to a tag. – Carra Mar 7 '18 at 14:16
  • @Carra 'store category_id in article table' I meant if we put restriction that an article can have only one category in that case. – Kuldeep Singh Mar 7 '18 at 14:20
3

We discussed few plans but wanted to know what would be the best database schema to store Articles in MySQL Database.

  1. One table each for article, category, tag and then anoter two tables for many-to-many relation between article-category and article-tag

  2. 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?

As you said, categories could become a many-to-many relation, using 2. will not help you to do that. In addition, as @Carra said:

'tags are comma-sperated values in article table '. That's usually a bad idea. Especially if you have to find all articles linked to a tag.

1 seems to be the best option you can have: easaly scalable and robust.

Another doubt we have is how to store images. Maximum it can be upto 3-4 images per article.

  1. Shall we create another table for images and store as CLOB/BLOB and then in another table store the relation between article and image ?

  2. Or instead of CLOB/BLOB, we store it as base64 encrypted string in database and decrypt on client side ?

  3. Or we upload the actual image to some file hosting solution (e.g. AWS S3..?) and then store the image URL in table ?

It's not an easy choice. Between 1. and 2., I'll choose because base64 image weigh 20% more than a BLOB. Unless you can use BLOB for technicals reasons (example).

Choosing 3. depends of a lot of things : number of images you want to store, size of each image, how many access can you have per minute, do you want to control it or do you want it from an external source... And so on.

1
  1. Article/category/tag.

Check if your article/category is one to one relation or many to many. You can always start with a one to one and move to a many to many relation later.

Tags. Saving them as comma separated values will give difficulty if you want to search on them. You'll have to do a partial text scan on all your articles to find the correct item. If you store them separately you can put an index on it and have faster select queries.

  1. Images

These can be rather big and thus put some extra load on your db. Therefore it might be a good idea to save the images themselves in a separate db or on disk and add a link to them from your main db. See these questions for more detailed info.

  • Yes. we want to search using tags. So I believe its better to store them in different table. – Kuldeep Singh Mar 7 '18 at 17:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.