Relationship between tables

Question

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 .

Mukesh Nayak · 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 Elawad · Answer

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