Member_2_8198376
asked on
How do I limit the choices in a SQL Server column
In SQL Server management studio, I have a table called, Common.
Common has a column called, FLSA.
The FLSA column can only have one of two values entered:
1. Exempt
2. Non-Exempt
If I use a Check Constraint, then it will be more difficult for my Asp.Net web application's Entity Framework to pull the choices when I want to populate a drop down list in the HTML.
If I use a reference table called FLSA, do I add those two values directly and then create a relationship between the Common table and the FLSA table; OR do I use a check constraint on the FLSA table?
If I add the two values directly to the FLSA table, do I need a separate primary key field like Flsa_Id OR can I just make the FLSA column itself the primary key?
If I make the FLSA column of the FLSA table the primary key, how do I prevent any application from adding more values. There should only be those two values unless I manually change it through SSMS.
Common has a column called, FLSA.
The FLSA column can only have one of two values entered:
1. Exempt
2. Non-Exempt
If I use a Check Constraint, then it will be more difficult for my Asp.Net web application's Entity Framework to pull the choices when I want to populate a drop down list in the HTML.
If I use a reference table called FLSA, do I add those two values directly and then create a relationship between the Common table and the FLSA table; OR do I use a check constraint on the FLSA table?
If I add the two values directly to the FLSA table, do I need a separate primary key field like Flsa_Id OR can I just make the FLSA column itself the primary key?
If I make the FLSA column of the FLSA table the primary key, how do I prevent any application from adding more values. There should only be those two values unless I manually change it through SSMS.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Remember you are talking about the data layer of the application...
It seems you are sure the column can have just two values so the best data type is bit which can have values 0 or 1 but nothing else.
And your ASP.NET application should interpret/convert these bit data into the user friendly format. Even the SQL query can return text based on the bit column data:
SELECT CASE <YourBitColumn> WHEN 1 THEN 'Exempt' ELSE 'Non-Exempt' END AS OutputColName
FROM YourTable
No constraints, no reference table, no overhead...
It seems you are sure the column can have just two values so the best data type is bit which can have values 0 or 1 but nothing else.
And your ASP.NET application should interpret/convert these bit data into the user friendly format. Even the SQL query can return text based on the bit column data:
SELECT CASE <YourBitColumn> WHEN 1 THEN 'Exempt' ELSE 'Non-Exempt' END AS OutputColName
FROM YourTable
No constraints, no reference table, no overhead...
In my opinion CHECK CONSTRAINT is the easiest in this case from database table architecture/structure POV and you could use hardcode the two values/choices in the drop down list instead of getting them from the table right? Alternatively IF you want to then just run a
Open in new window
and that will give you the only two values and the constraint will make sure nothing else can get into that table column.