Creating database relationships while designing tables?

Asked By 0 points N/A Posted on -
qa-featured

I want to create database relationships like one to one, one to many and many to many relationship.

I am using SQL Server 2008 R2. Can anyone please tell me that how can I implement one-to-one, one-to-many and many-to-many relationships while designing tables?

 

SHARE
Best Answer by dainy
Answered By 0 points N/A #92779

Creating database relationships while designing tables?

qa-featured

Hi Nadeemrao,

I'll try to explain it in a simple way: 

  • One to one relationship:
  • It means each row in Table A is linked to only one row in Table B.
  • For example:
  • Table A:
  • user_id     first_name     last_name
  • 1                 brad                pit
  • Table B:
  • user_id           email_ad                password
  • 1                       ****@mailcom.     1234
  • The user_id 1 of Table B is brad pit in Table B. This type of relationship is actually not often used in real world. Maybe in MS Access this is useful because of the limited number of columns per table issue. In order to accommodate more than 255 columns we could split the table into two and create a one to one relationship.
  • One to Many Relationship:
  • It means each row in Table A can have zero, one, or many related rows in Table B but not vice versa. This kind of relationship are also referred as parent and child relationship or master and details relationship. This type of relationship is the most commonly used model.
  • For example:
  • Orders Table:
  • Order_id        Customer_Name           Delivery_Date
  • 1                       brad pit                            1/1/2012
  • Order_Details Table:
  • Order_id       Item_id        Quantity
  • 1                     1                    5
  • 1                      2                   1
  • Many to Many Relationship:
  • It means each row in Table A can have many related rows in Table B and vice versa.
  • For example:
  • Reusing the Orders table, and Order_Details table above, we add Items table.
  • Items Table:
  • Item_id         Description
  • 1                    sunglass
  • 2                    t-shirt
  • The Order_Details Table above created links to Orders table and Items table and causes the many to many model.  

Hope this helps.

Best Answer
Best Answer
Answered By 0 points N/A #92782

Creating database relationships while designing tables?

qa-featured

Database relationships are used to define the relationship between entities of Databases.

I’ll explain these before I tell you something on how to create these.

One-to-one relationship:

This is a relationship in which one entity is linked with other in such a way that only one link can be made. E.g. one person manages one department and one department is managed by one person

One-to-many relationship:

This is a relationship in which one entity is linked with other in such a way that multiple links can be made with one entity. E.g. a person can have multiple cars and multiple cars can be owned by one person.

Many-to-many relationship:

This is a relationship in which entities can have multiple relationships with each other. E.g. one writer writes multiple books and one book can be written by multiple authors.

In design mode of table, write click on field and select relationship option from the menu. A window will be displayed, here you can define relationships.

Login/Register to Answer

Related Questions