Uniqueness between 3 fields

Question

Hi all...

I need a logic in SQL Server database end -

I have 4 master tables -

1. EmployeeMaster

1 - Abhineet

2 - Neha

3 - Jaya

etc - 55 employees (1,2,3 - auto increment primary key)

--------

2. SubjectMaster

1 - Java

2 - Project Management

3 - Time Management

4 - Oracle

etc - 100 subjects(1,2,3,4 - auto increment primary key)

-------------

3. OtherSubjectMasters

1- AJAX

2 -Query String

etc - 90 records (1,2,3... auto increment primary key)

-----------

4. Ouarters -

Q1(April2016-June-2016)

Q2(July2016-September2016)

Q3(October2016-December2016)

Q4(January2017-March2017)

-----------

Now, from front - end, the dropdownlist is binded with SubjectMaster.

Incase, the user wants to insert other subject (not present in SubjectMaster table, then he/she inserts othersubject in OtherSubjectMaster table)

---------

Transaction table -

ID	EmpID	SubjectID	OtherSubjectID	Quarter
1	1 (Abhineet)	1	0	Q3(October2016-December2016)
2	1 (Abhineet)	0	2	Q4(January2017-March2017)

Here, if user enters subject from dropdownlist(subjectmaster table), then "SubjectID" field will have the ID of Subject and OtherSubjectID will have 0.

----

Logic that I need -

I want to check that one employee - is allowed to attend one training in one quarter.

Eg - here now, Abhineet will not be able to attend the training of subject id - 1 in Quarter-3 (Q3).

He may attend training of other subjects in this same quarter - Q3.

How do we get uniqueness between - Employee , Subject and Quarter ??

Please guide....

Riddhi Valecha · Answer

Hi.. How to check uniquess from stored procedure.... Do you have the query for the same ? Because the record gets updated .. Eg - I select a record from Gridview  and make the EmployeeID SubjectID same - still - instead of giving error it gets updated. How to stop the updation at this point ? Please guide and help !

Shubham Kumar · Answer

Hi  if you have to check the uniqueness of more then a column so you should have to check from back end with stored procedure Proc make it fast to check

Riddhi Valecha · Answer

No no.. This is not what I want... I have made unique keys for four columns in sql server - I want that I want to check this uniqueness in asp.net c# (front-end)... I want to know which approach is better - back-end keys or front-end checking ??

Jithil John · Answer

Try something like this http://www.c-sharpcorner.com/UploadFile/20abe2/how-to-check-user-name-or-email-availability-using-Asp-Net/

Riddhi Valecha · Answer

Dear  This is simple 3-tire application.. This is not MVC...

Jithil John · Answer

Try remote validation in asp.net mvc

Riddhi Valecha · Answer

I suppose this will not be a good practice to keep a check in the database end  and instead check the uniqueness in front-end. Do anyone know how to we do this ?? Please explain..

Riddhi Valecha · Answer

Hi.. Sorry for this long issue writing. In back-end - I have put constraint on 3 columns (EmployeeID SubjectID LevelID Quarter) - unique key. Now I am inserting bulk data from front-end (using foreach loop)  for each record I need to check and provide necessary user-friendly messages. How do I do that ?

Shubham Kumar · Answer

hi Riddhi hope you doing well Please don't see the whole condition together divide your problem in chunks from that your problem goes to solve simply.Now come on your problem the problem is you hv to check the condition for every employee should attend one training program in one quarter. ok just check as like. 1st: is employee attend any training program if yes then who quarter program else do whatever you want 2nd if yes then check which subject if previous subject and current subject are same so not allow 3rd if subject is not same so then allow... and so on

Jithil John · Answer

You can use remote validation option in MVC For example http://www.c-sharpcorner.com/code/1704/check-username-exist-or-not-using-json-in-mvc.aspx

Riddhi Valecha · Answer

Well.. How do I check that from font-end ? Also if this is set in back-end then lines of code will be less... Can you please guide to do a check from back-end ?

Jithil John · Answer

You can check it from the front end itself