Retrieving and saving database fields by ID

Question

Data in one table is referenced by ID to another Table. For example, I have a table of States with ids 1-50. In another table "User" I have a reference to that id ie. state= 4. When I need to update data back to "User", if the state changes, should my code be aware of the numbering of the state data? In other words, if the new state is Alabama id=1, i should enumerate before submitting to database? Or should the DataAccess layer search the datatables for theid for the new state?

Answer 1

The specifics of this depend on what your front end is, but in general you should pass around the id as well as the state name, so when the user selects Alabama, id 1 gets pased back to your DAL so it can set StateID in the user table directly to that with no additional selects.

E.g., if the front end is a web page,

<select>
    <option value=1>Alabama</option>
    ...
</select>

Answer 2

You should never need to perform an additional search check in the db, if your database has a proper foreign key on the user table column that references the states primary key column. Having this will always ensure a legit value is persisted to your user record.