Last post Dec 30, 2010 11:27 PM by nilsan
Dec 29, 2010 02:50 AM|dracs|LINK
I want to make a case record which would have many people associated with it. This case record would be made through webform and all people associated would be added during the creating case phase. The case information and person information are stored in
separate tables. The case id (PK of case table)and person id (pk of person table) would be stored together separately in a separate record table and together will form the primary key of record table.
What would be best way to accomplish this since all the primary keys would be provided(person ID) / generated at run time( case id, record id).
I also want to ensure data integrity so either all happens or nothing happens.
Any suggestions welcome.
Thanks in advance,
Dec 29, 2010 04:24 AM|abrly|LINK
If you are using sql server database, you can set a column as primary key and also you can set the same column as "identity" column and set the identity seed by 1.
so this column will be unique and will seed from database itself automatically.
Dec 29, 2010 04:59 AM|Saravanan M|LINK
Well, you can achive this goal using Surrogate Keys. Read more info from here,
Dec 29, 2010 11:26 PM|dracs|LINK
Hi folks ,
Thanks for suggestions. But my concern is not what to use as key but how to implement my problem.I want to make a case record using webform and want to store all information in sql server at backend. All the information will be filled in at runtime by user.
Any more suggestions.
Dec 30, 2010 12:10 AM|nilsan|LINK
You can have three tables :
CaseRecord (Case Record Detail)
User (can use Membership User table)
CaseUser (will contain CaseRecordId,UserID)
In your page you can create CaseRecord First after that In User Registration Page you can choose from available caseRecord and store them in CaseUser table along with User info that will be stored in User table.
I hope it make sense :)
Dec 30, 2010 04:43 PM|dracs|LINK
Thanks for your reply. Yes it makes sense and it is making thing clearer with what I want to accomplish. I have another small concern, I am trying to send automatically all updates of case when it is edited to all parties concerned. So Initially my plan
of action was to make it in a way that I could add people and make case simulataneously and on submit , all details are emailed to parties concerned. Later on I would like to add/edit users to case.
Is it viable to accomplish with the approach we discussed.
The approach you suggested definitely lets me accomplish in creating case and adding users along the process or later on if there is a lost connection while adding users to case. Since this case can be manually searched.I think it can be probably done where
I can have an edit user button for case and it will populate all users on that page and their information can be updated. Am I on right track?
Dec 30, 2010 11:27 PM|nilsan|LINK
I am trying to send automatically all updates of case when it is edited to all parties concerned
Yes you can do that, you just have to get all the users for given Case and email to them updated details. You can achieve that from front end as well as back end. You can send mail from your Update stored procedure it self :)
Since this case can be manually searched.I think it can be probably done where I can have an edit user button for case and it will populate all users on that page and their information can be updated. Am I on right track?
Yes, you are :)