DotNetbeginner DotNetbeginner - 1 month ago 7
SQL Question

connecting multiple tables in a database using keys in sql

I have a table named student with columns studentid, name, address, email, phone.
Each student may have multiple email-id and multiple phone numbers. I want to record all emails and phone numbers too. So, I manipulates the student table columns by removing email-id column and phone column And created another table studentemail with columns emailserialnumber , email-id. And one more table studentphone with columns phoneserialnumber , phone. student table consists of studentid, name, address columns. should I add anymore columns to student table to maintain relation with other tables. Now I have three tables. now how can I relate the tables using keys. I mean like which column should I make primary key and foreign key so that three tables are connected.

Thank you

Answer

Your studentemail and studentphone tables simply need another column to reference the student table. In your case, this would be the studentid column.

You can then bring the data together in a single query. I should note that you might want a column that specifies what the email and phone numbers are -- home, mobile, work, and so on.

Comments