2

Hi I am in middle of designing DB and Got a little bit confused about DB Design.

I am Using Hibernate as ORM Tool ahd mapping between Page and Step entity in hibernate.

@ManyToMany(fetch = FetchType.LAZY,cascade=CascadeType.REFRESH)
@JoinTable(name = "page_childstep", 
joinColumns = @JoinColumn(name = "page_id"), 
inverseJoinColumns = @JoinColumn(name = "step_id"))

and automatic DB Scripts got generated by Hibernate Like this.

CREATE TABLE `page_childstep` (
    `page_id` BIGINT(20) NOT NULL,
    `step_id` BIGINT(20) NOT NULL,
    PRIMARY KEY (`page_id`, `step_id`),
    INDEX `FK_page_childstep_step_id` (`step_id`),
    CONSTRAINT `FK_page_childstep_step_id` FOREIGN KEY (`step_id`) REFERENCES `teststep` (`id`),
    CONSTRAINT `FK_page_childstep_page_id` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

where as in my manual design I have designed it by this way for some other mapping table

CREATE TABLE `page_page` (
    `page_id` BIGINT(20) NOT NULL,
    `pageid` BIGINT(20) NOT NULL,
    UNIQUE INDEX `uk_page_page` (`page_id`, `pageid`),
    INDEX `fk_page_page_page_id` (`page_id`),
    INDEX `fk_page_page_pageid` (`pageid`),
    CONSTRAINT `fk_page_page_page_id` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`),
    CONSTRAINT `fk_page_page_pageid` FOREIGN KEY (`pageid`) REFERENCES `page` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

is there is any difference between them Using Primary vs Unique Key. and generating Index for inverseJoinColumns = @JoinColumn(name = "step_id") is necessary? if write native query then what is the way to make join?

Page <-> page_childstep <-> step 

OR

step  <-> page_childstep <-> Page 

just making indexing changes the way join works in general not MariaDB specific.

ThinkTank
  • 121
  • 4

0 Answers0