Help needed on database schema design

database-design

I work as full stack developer, but never really spent time on Database designs. I had written code to retrieve data out of DB, but never ever created a schema from scratch. So, I thought of doing some side project to understand how database design involves.

I had decided to create a Resume Builder Application. Here are some of the features that I had thought about:

  • User can sign up to create his/her own resumes.

  • User can have multiple resumes. (only applicable for paid users, for free users only one resume)

  • Resume can be built from the UI (from available components) and user can save it as draft.

  • Resume can be published via url.

  • Published resumes stats like visitors count needs to be calculated.

  • User can delete the resume.

  • User can upload his/her pic.

  • User can add description about themselves.

Given all these, I have come up with this schema:

https://ibin.co/3CoJEfeQTuAp.png

Here are the questions I have:

  1. I have a general knowledge on RDBMS, so my schema targets databases like Oracle, MySql etc. But can we choose NoSql for this problem? If so why?

  2. Is my schema correct? Does it work on large data with ease?

  3. I thought of saving the resume layouts (layouts for each resume is decided by the user via say UI) in JSON format. Is it a good practice to use JSON datatypes?

  4. ResumeData table serves to store the user data for each resume. Is it a good to have a separate table for the same?

  5. If the answer for the first question is RDBMS, which database you will prefer? I had decided to use PostgreSql.

Thanks a lot for spending time on answering these questions.

Best Answer

  1. I would suggest not storing images in the database.
  2. Don't store unsalted password in the database. Hint: use pgcrypto
  3. In PostgreSQL, don't use varchar where text would otherwise work, because it's implemented the same way except it doesn't have the check.

Don't use the JSON datatype unless you have good reason. Use jsonb, which you can operate on and index.

Related Question