I have just merged several identical legacy tables, DeptL1, deptL2, ..., deptL*n*, where L is a reporting roll-up level, into one table, Dept, with a new ReportingLevel field. In my previous model, several entities, e.g. Employee, had an 'FK' relationship only with DeptL1, where they now have an 'FK' relationship with the whole Dept table.
Is there a way for me to use metadata, or extend a dynamic edit control, to restrict dropdown items for the Dept field in Employee, to only include items from Dept with a reporting level of 1?
BONUS: Because I'm very concerned with my business logic right now, and also somewhat lazy, bonus points for a tip on how to limit the actual FK relationship in MSSQL and MySQL.