0

I am creating a C# application with a lot of tables in it. I have created an entity relationship diagram/ model but am not sure how to implement it into a working SQL database. I need help with things like:

  • Do I need to create a separate tables in my database for relationships in between my many to many relationships?

  • How to treat one to many and one to one relationships.

  • When to do foreign and composite keys(and how to do composite keys).

If anyone can help me with any of these points or any other points that you think will be helpful to me I would be very grateful.

(I would show my entity relationship but you have to have a certain reputation to link them which I don't have)

mot375
  • 99
  • 1
  • 13

1 Answers1

0

Do you need to create a separate tables in your database for relationships in between my many to many relationships?

Yes, it's part of what we call object oriented design, which is commonly used in the industry, manipulating through your tables will be easier, and a shorter query for joining tables when retrieving data will be shorter and faster/efficient.

How to treat one to many and one to one relationships.

Example, there's a company with many addresses, then you must have one table for Addresses (ex. t_Address) which will be used as a foreign key in a table for your companies (ex. t_Company). This is one(company) to many(address) relationship.
Hence, One to one relationship is you must have a link back between the tables. Example, a table of addresses (ex. t_Address) for student have a foreign key of Student_ID and the student table (ex. t_Student) also have a foreign key of Address_ID. This is a one to one relationship, where you can search for a student just by using the address, or the other way around.

When to do foreign key? If only if there's a need for you to have relationship between those tables.

When to use composite keys?
Well nowadays it's no longer efficient for you to use composite keys, it was mostly used in the past where some thought of having multiple primary key (or what you call composite key) is better than having only one primary key, but in reality it will just cause you problem because you're having 2 unique keys that will be referenced on other tables which will cause you a lot of time and possible errors in coding phase, because when you use the other table where your composite keys are referenced, you will be having a total of three unique columns just to identify one record, which makes it very inefficient.

How to do composite key? (well this might not be efficient but you can refer to this if it's party of a requirement in your project or whatsoever)
how do I make a composite key with SQL Server Management Studio?
http://www.1keydata.com/blog/2013/02/composite-key-in-sql.html

Community
  • 1
  • 1
JC Borlagdan
  • 3,318
  • 5
  • 28
  • 51