Linking records to database based on ID

Question

Hi all, I am working on a project using winforms in c#. The problem is that, I want to have a PersonID . I want to enter details (such as name, age profession et..) to the database, but I want those information to be linked to the PersonID in case I want to change any of the records that belogns to PersonID. For example I want to save address that is linked to Person ID. Then if I want to changed the address of that Person, I should be able to change the address by just typing PersonID of that person. I need someone to tell me please how best I can do it. Shall I write classes? Please advise me on how best I have to do it. I hope I am clear. Thanks in advance. Regards

Nitin Sontakke · Answer

If not impossible, it would be very difficult for me to guide you through the entire process. To teach everything in such an interactive way would be complicated. Half the time, you wouldn't even know what I am talking about. Unfortunately, with the amount of abstraction that is now implemented in typical enterprise application development, it becomes difficult to explain everything. Anyway. All I can do is give you some hints. From the tone of your comments, it seems this task sounds complicated to you. Let me tell you that it is not. This is very simple, routine, usual business application development scenario is probably about 1% of full application. Your person table looks okay to me. For second table where you are tracking changes. PersonId column cannot be a primary key because you will have several records of the same person. Let that table have it's own identity column just called Id, which has no business meaning. That table will have PersonId column (as it is having now) and that will refer to (foreing key) to main person table. Columns of second table doesn't look good to me where are you keep track of what is changed. There are two ways to handle this. 1/ Have a table with identical columns as the main table and add just one more column as Id. Then write a trigger on the main table. In the trigger add a record to second table with ONLY those columns where value is changed. In this approach there is only one record is generated even if all the column's value is changed. 2/ Create one generic table with columns like, table name, column name, old value, new value, user id, date time of change. Then write a trigger on the main table to append this table with appropriate information. In this approach there is one record generated for each column value changed. With this in place all you have to do is write several select statement to get data from these tables. Stored procedures is the most recommended way to handle all the database interaction.

Nitin Sontakke · Answer

While it is possible to this without classes, I recommend that you write those. Honestly, your question is not quite clear. The kind of thing you are doing is done routinely by millions of developers. So it is not clear where exactly you are having question. Is it back-end or front-end or both? Please start by creating some base tables in notepad and then share the structure here and then ask question. If you try something then it is easier for us to help.

naem name · Answer

Hi (Mr.) Sontakke, Thank you very much for replying my post. I'll try to do something based on the 2nd proposed solution. The solution is brilliant. Thank you, Regards

naem name · Answer

Additional Information: I want to have a database to hold the information of a person in one table. Information about the person can be: 1. PersonID 2. FirstName 3.Lastname 4.Sex 5.Occupation 6.Department. What I want to eventually do is that: I may want to have another tabel in the database called Job History (for example): For Job History Tabel, it can contain information about the Person. Job History table should consist of: 1. PersonID 3. Name of previous job 2. name of employer 3. duration of job 4. department. I want to like Job History Table (for example) to Person table in the database. I want to like those two table Via PersonID. A person may have more than one previous job. I want the person to add more than one job history and link the entered information to his PersonID, so that the information is linked to a certain person based on PersonID. I want to like those two tables through windows forms c#. I don't know how. I also want to be able to display any changes made to the database: For example, a person may change the name of his previous job, I want to have some sort of log so that if the person changes any entry, it is recorded that the person made a certain change to a certain entry at a certain time. Please advise me the steps I have to take to achieve the following tasks. I want to learn how please. Kindly advise on the guidelines. Regards,

naem name · Answer

Hello, Thank you very much for replying to my post. The problem is that, I have 2 tables in my database. The first one is for a person. The second one is to track changes. I want to link both tables via the PersonID. I want to make changes to any entry in table one (through windows forms). Let's say I want to change the lastname of a person with certain Person ID. I want to type PersonID in the windows form, then Select Which record I want to changed (for example firstname/sex/lastname) based on the provided PersonID. I also want to track the changes to any record. Let's say A person managed to change his last name, I want to create a form where the change is automatically detected. I want to detect any changes made to the database by any user. I want to have something like a log where the activities done to the database are recorded. I attached Tabels.zip. Thanks in advance.