January 9, 2014 at 6:38 am
It's been awhile since I posted this but I believe the second table tEmployeeStatus was used to maintain the status list so statuses could be added from a UI. You don't to have to have that table as the list could be maintained directly in the constraint but of course you lose some flexibility in regards to the above requirements.
January 10, 2014 at 12:59 pm
Divine Flame (1/9/2014)
I am not sure if I understand this proposed model correctly. In the proposed model, i don't see any importance of even creating the table tEmployeesStatus as StatusDescription is the only column in this table & this StatusDescription has already been written to the table tEmployees.
No, inserting a row into tEmployees with a staus that isn't already in tEmployeesStatus fails because the check function will not return 'Y' so that row would violate the check constraint. The tEmployeesStatus table has to be maintained separately. This is reasonable if it doesn't cause a big space problem - so if there are not more than about a few hundred distinct statuses, and not too many employees (ie it only makes sense when there isn't much data).
However, the whole thing is abominably badly written; probably needs NOT NULL on all the column definitions, in tEmployeesStatus the single column should be declared as the primary key, in tEmployees something should be the primary key, and that UDF should be much shorter, everything between BEGIN and END (not inclusive) could be replaced by a single return(select...) statement using CASE and IN.
Tom
January 10, 2014 at 1:09 pm
It was only written to help convey the idea behind the question. It's not production code.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply