Best practice for working with Foreign key fields

I have 2 tables, Word and State, State contains 2 cols, ID and CurrentState, it's 3 static rows are 1-Active, 2-InActive, 3-Other Word is the table I am adding rows to. It has 4 cols, ID, Value, Description and CurrentState. It has a foreign key to State on the column currentState Here is the working code I have that creates a Word, sets it's currentState field and persists it.

Word word = new Word(); 
word.setValue("someWord");
word.setDescription("some description for this word");      
State state = new State(1,"Active");
word.setState(state);   
worddao.saveOrUpdate(word);

The thing is this just doesn't look right. What is the best practice for creating the State instance so that I can create a Word which points to a valid State row. Is Enumeration an option here? I mean I could accidently create a State with ID = 5 and violate the foreign key constraint. I want to prevent this from happening in the first place. Any ideas?

Answers


The safe way to do this is to look up the state object instead of creating a new one. You can mark the entity as cacheable and also mark the query to retrieve State as cacheable to avoid unnecessary queries.

Word word = new Word(); 
word.setValue("someWord");
word.setDescription("some description for this word");      
State state = stateDao.findByState("Active")
word.setState(state);   
worddao.saveOrUpdate(word);

The other option (which I generally follow) for master/reference data table is to not have an numeric ID. Just have one column which is the String representation of it and use that as the foreign key column.

The advantage with this is that when you are looking at data in the database - the word table the state will be easy to interpret. You don't have to do one more mental lookup to understand the data.

The other advantage is that caching becomes easier. We just need to cache the entity and not the query, since the lookup is always by primary key.

Later on if required you can add another column which is a display label.


Need Your Help

how to avoid duplicates in a has_many :through relationship?

ruby-on-rails ruby duplicates has-many-through has-many

How can I achieve the following? I have two models (blogs and readers) and a JOIN table that will allow me to have an N:M relationship between them:

Getting longitude,latitude without setting it all app life

iphone objective-c location

I want simple get lan,lon valus at the time application start,I dont want to update every second those values as I am changing my possition. I am using this code:

How to populate a Listbox in C++ with all folders in a directory

c++ listbox openfiledialog

I would like to know how to list all folder in a directory in a listbox. I am using Visual C++.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.