I am developing a E-Commerce site.
On the database side, I only have two tables for status. One table named StatusGroup (eg, status for product, status for order, etc), and the other table named GlobalStatus. It is a one-to-many relationship. It records all the status based on the different statusGroup.
Now my question is, on the C# side, how can I handle the status?
For example, when I create a new product, there is a status field for it. For now, I am hard-coding the statusId (grab from the GlobalStatus table from the database and assign to the product).
For example, productToCreate.StatusId = 111; It works but looks so stupid.
When User change the value in the StatusGroup table, for sure my c# code won't work because it is hardcoded.
I was thinking about using the enum approach. To generate a enum list and get data from the database. But in my second thought, I guess it won't work because enum is for design time and if someone change the value in database again, it won't work...
I am confused and I am wondering how to proceed. All I want is something that can assign the statusId to my object, even if someone change the value in the database, it can still get the correct statusId. (for c#, input by the statusName but not the statusId)
Don't expose the StatusID field to your application at all.
Add an indexed (unique) field StatusDescription to your Status table, and always join to this table on StatusID to present the corresponding StatusDescription to the application, and to users. Perhaps define a convenience view ItemView that incorporates this join.
Present the StatusDescription fields to your user when a Status value must be selected.
When updating the Status for an item:
update ItemTable
set StatusID = (
select StatusID from Status where StatusDescription = @StatusDescription
)
where ...
If various conditions are met, you might even find that this SQL works just fine:
update ItemView
set StatusDescription = @StatusDescription.
where ...