使用外键字段的最佳实践 [英] Best practice for working with Foreign key fields

问题描述

我有2个表，单词和状态， State包含2个列，ID和CurrentState，它的3个静态行分别是1-Active，2-InActive，3-Other Word是我要向其中添加行的表.它具有4个列，ID，值，描述和CurrentState. 它在currentState列上具有State的外键 这是我创建一个Word，将其设置为currentState字段并将其持久化的工作代码.

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);

问题是这看起来不正确.创建State实例的最佳实践是什么，以便我可以创建一个指向有效State行的Word.枚举是一种选择吗?我的意思是我可能会意外地创建一个ID = 5的State并违反外键约束.我想首先避免这种情况的发生.有什么想法吗?

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?

推荐答案

执行此操作的安全方法是查找状态对象，而不是创建一个新的对象.您可以将实体标记为可缓存，也可以将查询标记为检索状态为可缓存，以避免不必要的查询.

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);

主/参考数据表的另一个选项(我通常遵循)是不具有数字ID.只需拥有一列它的字符串表示形式，并将其用作外键列即可.

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.

这篇关于使用外键字段的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！