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

Question

1.00/5 (1 vote)

See more:

, +

I have a scenario of of three classes .I am planning to make the database for it .The relationship between them is :
1 customer is related with many items and many dv-vouchers.
1 item is related with many customer and many dv-vouchers.
1 dv-vouchers is related with 1 customer and many items .

XML

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 of database tables for above classes along with fk_constraints and relationship tables ?
Second Do I need to do both an Insert and Update the relationship tables along db table ? Please Help .

Posted 2-Dec-13 17:39pm

Member 8840306

Updated 2-Dec-13 22:29pm

v9

Add a Solution

Comments

agent_kruger 3-Dec-13 2:13am

what actually you want to do with maintaining the relations?

Maciej Los 3-Dec-13 2:14am

Is it proper design?
Customers table should store items and dvouchers; Items table should store customers and dvouchers and Dvouchers should store customers and items.
I would say: all in one, one in all.
Please, be more specific and describe more details. Use "Improve question" widget to upgrade your question.

Member 8840306 3-Dec-13 5:04am

Now see my improved question.Hope it is clear now.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2013-12-03T03:14:00

Simple enough.
The answer to your second question is Yes.

SQL

CREATE TABLE customers (
    CustomerID NUMBER(8)
    )
/

ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY (CustomerID)
/

CREATE TABLE Items (
    ItemID NUMBER(8)
    )
/
ALTER TABLE Items ADD CONSTRAINT Items_pk PRIMARY KEY (CustomerID)
/

CREATE TABLE dvouchers (
    DVoucherID NUMBER(8),
    CustomerID NUMBER(8)
    )
/
ALTER TABLE dvouchers ADD CONSTRAINT dvouchers_pk PRIMARY KEY (CustomerID)
/

ALTER TABLE dvouchers
  ADD CONSTRAINT dvouchers_Customers_FK FOREIGN KEY (
    CustomerID
  ) REFERENCES Customers (
    CustomerID
  )
/

CREATE TABLE CustomerItems (
    CustomerID NUMBER(8),
    ItemID NUMBER(8)
    )
/

ALTER TABLE CustomerItems 
    ADD CONSTRAINT CustomerItems_pk PRIMARY KEY (
        CustomerID,
        ItemID
        )
/

ALTER TABLE CustomerItems
  ADD CONSTRAINT CustomerItems_Customer_FK FOREIGN KEY (
    CustomerID
  ) REFERENCES Customers (
    CustomerID
  )
/

ALTER TABLE CustomerItems
  ADD CONSTRAINT CustomerItems_Items_FK FOREIGN KEY (
    ItemID
  ) REFERENCES Items (
    ItemID
  )
/

CREATE TABLE dvoucherItems (
    dvoucherID NUMBER(8),
    ItemID NUMBER(8)
    )
/

ALTER TABLE dvoucherItems 
    ADD CONSTRAINT dvoucherItems_pk PRIMARY KEY (
        dvoucherID,
        ItemID
        )
/

ALTER TABLE dvoucherItems
  ADD CONSTRAINT dvoucherItems_dvouchers_FK FOREIGN KEY (
    dvoucherID
  ) REFERENCES dvouchers (
    dvoucherID
  )
/

ALTER TABLE dvoucherItems
  ADD CONSTRAINT dvoucherItems_Items_FK FOREIGN KEY (
    ItemID
  ) REFERENCES Items (
    ItemID
  )
/