1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.