Relationship between tables

Hello Guys

please help me , i need to their exparience in databse to put good relationship between tables inorder its works as good functionality. i will be upload tables as photos .

1. tbl_Emplyee ( EmpId(identity) , EmpNo(Pk)

2. tbl_Items (ItemId (identity) , PartNo (PK)

3.tbl_Custody (CustodyId(identity) , EmpNo(pk in tbl_Employee) , (PartNo (pk in tbl_Items)

i need to create relationship between three tables above

4. tbl_SimCard (SimId (identity) , (ServiveNo Pk )

5. tbl_SimCustody (SimCustodyId (identity) ServiceNo Pk in tbl_SimCard) (EmpNo Pk in tbl_Employee)

Thank You to any one supported to me .

Hi Abdalla,

We need to know different type of relationship first. Relationship between tables in database can be of following type.

1. One to one

2. One to many.

3. Many to many

Lets consider one to one relationship between table first. Let suppose we are storing Student personal information in one table such as age, name , etc and its corresponding academic detail in other table such as class, etc. So here one student can be associated to one class only. So both table will have one to one relationship.

Simillarly if we store student information in one table and its address information in other table. Then a student can have one address as present address and other address as permanent address. So here student can be associated to multiple address.So tables will have one to many relationship.

For the last type of relationship , let's consider a scenario we are keeping Teachers information in one table and we do have class information in other table. Now a teacher can be associated to multiple classes. For storing association between Teacher and class we need a table where we will have TeacherID as ClassID to define which teacher is associated to which class. Now this intermediate table reflects many to may relationship.

To know more about different type relationship please refer link below :

http://www.tech-recipes.com/rx/56738/one-to-one-one-to-many-table-relationships-in-sql-server/

Now in your example above tbl_Employee has got EmpNo as primary key and tbl_item has got PartNo as primary key. And the the third table tbl_custody has got EmpNo and PartNo as a foreign key.

Again ServiceNo is primary key for the table tbl_SimCard. Table tbl_SimCustody has got EmpNo and ServiceNo as foreign Key.

Regards,

Mukesh

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mukesh Nayak8y ago
Accepted answer
Hi Abdalla,

We need to know different type of relationship first. Relationship between tables in database can be of following type.
1. One to one
2. One to many.
3. Many to many

Lets consider one to one relationship between table first. Let suppose we are storing Student personal information in one table such as age, name , etc and its corresponding academic detail in other table such as class, etc. So here one student can be associated to one class only. So both table will have one to one relationship.

Simillarly if we store student information in one table and its address information in other table. Then a student can have one address as present address and other address as permanent address. So here student can be associated to multiple address.So tables will have one to many relationship.

For the last type of relationship , let's consider a scenario we are keeping Teachers information in one table and we do have class information in other table. Now a teacher can be associated to multiple classes. For storing association between Teacher and class we need a table where we will have TeacherID as ClassID to define which teacher is associated to which class. Now this intermediate table reflects many to may relationship.

To know more about different type relationship please refer link below :
http://www.tech-recipes.com/rx/56738/one-to-one-one-to-many-table-relationships-in-sql-server/

Now in your example above tbl_Employee has got EmpNo as primary key and tbl_item has got PartNo as primary key. And the the third table tbl_custody has got EmpNo and PartNo as a foreign key.

Again ServiceNo is primary key for the table tbl_SimCard. Table tbl_SimCustody has got EmpNo and ServiceNo as foreign Key.

Regards,
Mukesh
Abdalla Elawad8y ago
But i will try to add tbl_SimCard to tbl_Items , may be this correct , you with agree me Mr.
Mukesh Nayak