I am trying to figure out how to set up my schema for an SQL Server database. I would like to figure out how to update a record or insert a record given certain criteria and how to distinguish the difference and when to use what one.
My tables are normalized to the third normal form with a distinct primary key and foreign key relationship for each table.
My example is this:
Lets say I had a computer whose name was DELLCOMP and I had a table with the computer list in it with all the computers on my network. Then I had another table with the CD drive information which had a foreign key tying the CD drive information back to the computer. Let say for instance I had two of the same CD/DVD drives in the CD table because the computer had two exact same drives installed.
If the only difference was the primary key id how do I know which record to update. Lets say upon first entry all I can input is the computer id the CD drive's belong to and the name of the CD drive. Perhaps I don't have the drive letter associated with the two drives yet. How do I distinguish between the two records to update one but not the other, for instance, if I get the drive letter later of one but not the other. And how do I tell when I should insert instead of update.
I think splitting the table out might be an option or maybe changing the PK to something other than an ID but I am not sure on this. Any ideas would be helpful. Thanks!
This post has been edited by kyle_denney: 26 August 2013 - 09:35 AM