Perhaps I misread the question. I took the following:
"My designer tells me to issue a select and find the no of rows for all fields , if it is less than 5 rows (values) this should be a check constraints."
to mean that if you have a table that is only five rows (or fewer) then you should drop the table and in referring tables use a hard-coded check constraint.
create table bob references joe( joecol )
create table bob check in (1, 2, 3, 4)
I don't necessarily agree that this approach is a 'best practice' because you have to check constraint every table that refers to it -- and if you ever add one more then you have to change every single one.
But really, if your table is that small then it would probably fit on a single page and be in memory virtually all of the time -- so there would be precious little difference between a check constraint and a table lookup.
Yes, that's how I read the requirement too. It seems that the designer has come up with a "rule of thumb" (ROT) that any foreign key with less than 5 distinct values should be replaced by a check constraint. What a silly idea! Surely the choice of whether to use a check constraint or a table depends on more than just the number of values - it depends on whether the values form a fixed domain or a variable set of values. For example, if one column was DEPARTMENT_NO and we currently only had 4 departments, it would be perverse to make this a check constraint. Or maybe we have 30 departments, but only 4 of them have created records in the Budget table, ...
I think that we can safely say that until we understand the question, we have precious little chance of answering it. Maybe thirumaran will return and explain what they want/need in more detail, but without that I think we're beating a dead horse!