When is it beneficial at all to create a seperate table for a one to one relationship? I've got a db with thousands of records. I have about 5-10 employees who will be assigned these records. Each week about 300-500 ne records will be added / dropped, requiring a script to assign these. Is it good / bad practice to put the assignedTo field on a seperate table?
Also. I'm running this db in access, about to switch to SQL server. Each record has a status that must be limited to 10 or so text responses. Should I change this to a number code with a relationship linking that number code to the text version of the status? Or can I continue to rely on code / db design to limit responses to my given selection.
i don't think you need to bother with a separate table
just update the assignedTo column directly
regarding your second question, i think it's largely a question of application simplicity whether you want the added complexity of a surrogate key for the text messages
i would still have a second table, to ensure that only authorized texts are used (via foreign key check), but a few extra bytes per row in space, for a few thousand rows, is not going to substantially affect your total space requirement (not in this day of terabyte drives), but it will allow you to avoid an extra join to retrieve the text for every query that needs it