0

I am designing DB schema for my website.I have a job_history table which will contain the job related information. This is the table structure:

CREATE TABLE IF NOT EXISTS `website`.`job_history` (
  `job_history_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `designation` VARCHAR(255) NULL,
  `started` DATE NULL,
  `left` DATE NULL,
  `work_summary` VARCHAR(3000) NULL,
  `company_id` INT UNSIGNED NULL,
  `looking_job_change` TINYINT(1) NULL,
  PRIMARY KEY (`job_history_id`),
  INDEX `fk_job_employment_company1_idx` (`company_id` ASC),
  CONSTRAINT `fk_job_employment_company1`
    FOREIGN KEY (`company_id`)
    REFERENCES `website`.`company` (`company_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB

What I am confused in: since the MYSQL has now the power of JSON, why shouldn't I place a few columns inside the JSON. e.g designation, started, left, work_summary are kind of metadata fields explaining only the details(of job) which will not(or very unlikely) be going to be searched using WHERE clause. And if they are going be searched(rarely) MySQL has well-supported functions for that as well.

Why not put them as a JSON datatype as:

{
  "work": {
    "designation": "developer",
    "started": "may, 2018",
    "left": "june, 2019",
    "summary": "I was a software developer here"
  }
}

New structure would be:

CREATE TABLE IF NOT EXISTS `website`.`job_history` (
  `job_history_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `work` JSON NULL,
  `company_id` INT UNSIGNED NULL,
  `looking_job_change` TINYINT(1) NULL,
  PRIMARY KEY (`job_history_id`),
  INDEX `fk_job_employment_company1_idx` (`company_id` ASC),
  CONSTRAINT `fk_job_employment_company1`
    FOREIGN KEY (`company_id`)
    REFERENCES `website`.`company` (`company_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB

Why I want to do it? - it would be easy to add new fields from frontend without even touching the Database. Suppose in future I want to a add new field: why you left at the front-end. I don't even need to change the table structure.

What are the pros and cons of using it this way?

Please give your valuable thoughts. Thanks

The Coder
  • 3,447
  • 7
  • 46
  • 81
  • Why not go further and store everything in document database? It is true that modern db supports non-atomic values but proper normalization could be the key if performance is important. – Lukasz Szozda May 05 '19 at 18:09
  • 1
    [Storing JSON in database vs. having a new column for each key](https://stackoverflow.com/questions/15367696/storing-json-in-database-vs-having-a-new-column-for-each-key) – Lukasz Szozda May 05 '19 at 18:18
  • Few reasons: 1). I only know MySQL and don't have time to learn NOSQL 2). that old RDBMS vs NOSQL war 3). My data would be quite relational in nature. Consider that only option I have is MySQL – The Coder May 05 '19 at 18:19
  • In reality there is no war at all. With proper normalization and indexing you get better performance, with JSON flexibility(as long as you don't want to make FK from inside JSON plus it could be indexed too). So the answer is as always "it depends". – Lukasz Szozda May 05 '19 at 18:20
  • After reading the link you suggested, I think my decision of using JSON is correct. Why? Non of the field would be PK or FK, none will be indexed. They are just like key-value pair. Non will be used in search query. – The Coder May 05 '19 at 18:29
  • And if I ever(very unlikely) needed to search the data inside the JSON, MySQL has support for that as well now. I am not bothered about the perfromance of that performance as those operations would not be very performance demanding, I am sure for future. – The Coder May 05 '19 at 18:33

0 Answers0