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.