CODEWITHSUNDEEP
sqldatabasesqlite
user3056831
user3056831

Reputation: 163

How i can make tables for the given many to many related classes?

I have a scenario of of three classes .I am planning to make the database for it .The relationship between them is :

public Customer
 {
   int cust_id;
   list<items> items;
   list<dvouchers> dvouchers;
 }
public items
 {
   int itm_id;
   list<Customer>  customers;
   list<dvouchers>  dvouchers;
 }
public dvouchers
 {
   int dv_id;
   Customer customer;
   list<items> items;
 }

First of all what can be the design for database tables for above classes, fk_constraints and relationship tables ?

Second Do I need to perform Insert and Update operation on both the relationship tables along db table ? Please Help .

Upvotes: 0

Views: 77

Answers (2)

DoNotArrestMe
DoNotArrestMe

Reputation: 1279

1. You need two tables Customer_Item and Item_Dvoucher for relationship many-to-many and field cust_id in table Dvoucher for one-to-one relationship.

2. You need to insert or update data in base tables Customer, Item and Dvoucher. Then you need to add or remove relationship in table Customer_Item and Items_Dvoucher. Also fill field cust_id in table Dvoucher.

Generated in mysql:

CREATE TABLE `Customer` (
  `cust_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
)

CREATE TABLE `Dvoucher` (
  `dv_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `cust_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`dv_id`),
  KEY `cust_id` (`cust_id`),
  CONSTRAINT `Dvoucher_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `Customer` (`cust_id`)
)

CREATE TABLE `Item` (
  `item_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
)

CREATE TABLE `Customer_Item` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `cust_id` INT(11) DEFAULT NULL,
  `item_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cust_id` (`cust_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `Customer_Item_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `Customer` (`cust_id`),
  CONSTRAINT `Customer_Item_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `Item` (`item_id`)
)

CREATE TABLE `Item_Dvoucher` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `item_id` INT(11) DEFAULT NULL,
  `dv_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `dv_id` (`dv_id`),
  CONSTRAINT `Item_Dvoucher_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `Item` (`item_id`),
  CONSTRAINT `Item_Dvoucher_ibfk_2` FOREIGN KEY (`dv_id`) REFERENCES `Dvoucher` (`dv_id`)
)

Upvotes: 1

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

In SQLite:

CREATE TABLE customer(cust_id INTEGER PRIMARY KEY, ...);
CREATE TABLE item(itm_id INTEGER PRIMARY KEY, ...);
CREATE TABLE dvoucher(dv_id INTEGER PRIMARY KEY, cust_id REFERENCES customer(cust_id), ...);
CREATE TABLE dvoucher_item(dv_id REFERENCES dvoucher(dv_id), itm_id REFERENCES item(itm_id));
CREATE TABLE customer_item(cust_id INTEGER PRIMARY KEY, itm_id REFERENCES item(itm_id));

As @Andrei solution to MySQL, using dvoucher.cust_id to link dvoucher to costumer, and tables dvoucher_item and customer_item to many to many links.

Upvotes: 0

Related Questions