0

I am developing a sample project between users and products. A user can sell multiple products and a product can be sold by multiple users. So I designed my db with a many to many relationship. Below are my tables:

create table `users` (
    `id` bigint(20) not null auto_increment,
    `first_name` varchar(100) not null,
    `last_name` varchar(100) not null,
    `email` varchar(100) not null,
    `password` varchar(255) not null,
    `phone_number` varchar(20),
    `created_at` timestamp default CURRENT_TIMESTAMP,
    `updated_at` timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    primary key(`id`),
    unique(`email`)
);


create table `products` (
    `id` bigint(20) not null auto_increment,
    `name` varchar(255) not null,
    primary key(`id`),
    unique(`name`)
);

create table `users_products` (
    `user_id` bigint(20) not null,
    `product_id` bigint(20) not null,
    `quantity`int not null,
    `price` DECIMAL(5, 2) not null,
    `description` text not null,
    `created_at` timestamp default current_timestamp,
    `updated_at` timestamp default current_timestamp on update current_timestamp,
    primary key (`user_id`, `product_id`),
    foreign key(`user_id`) references `users`(`id`) on delete cascade,
    foreign key(`product_id`) references `products`(`id`) on delete restrict
);

As you can see above, the products table name is unique because users can sell the same products.

Now I ask the question how this can be achieved using spring boot with JPA. I have seen tutorials with many to many with extra columns but the tutorials have just had an extra column with date time. But I want to perssit with the extra columns as mentioned above in the users_products table.

And also is the schema designed ok?

whackamadoodle3000
  • 6,684
  • 4
  • 27
  • 44
EI-01
  • 1,075
  • 3
  • 24
  • 42
  • Adding attributes to a join table: See, e.g., [Mapping Extra Attribute in a Join Table JPA 2](https://stackoverflow.com/questions/9816932/mapping-extra-attribute-in-a-join-table-jpa-2) and [JPA 2.0 many-to-many with extra column](https://stackoverflow.com/questions/23837561/jpa-2-0-many-to-many-with-extra-column) – K.Nicholas Sep 10 '17 at 00:18
  • Table `users_products` is a 'cross table', and formal relations in JPA remain OneToMany (ManyToMany is 'hidden'). I understand this table as `individual offer` or `individuual price list`, but i'm not sure. Is this Ordes? SalesTransation? – Jacek Cz Sep 10 '17 at 01:45
  • ... yes ... when longer look with cup of tea, this is `offer` table (not `done_sales`). IMO primary definition for such relations in JPA is Java code, nor SQL – Jacek Cz Sep 10 '17 at 01:49
  • @JacekCz `users_products` holds the price of each of product. A user or users can sell the same product but have different prices. The `users_products` table helps in distinguishing the difference – EI-01 Sep 10 '17 at 03:47

0 Answers0