I would go from the 3rd solution with a hash-table, as it does seem to be the cleaner design-wise. And I would delegate its management to the database!
After all, isn't this what relational databases excel at the most, creating relations between two entities (in your case, action and type)? Other advantage is you end up with a normalized schema (sure, so far, there is only one column to the type table, namely its name, but normalizing allows you to easily add additional attributes to the types should you need them in the future, which is why it is cleaner as a design).
The schema would be something like this:
Action table
action_id(PK) | name | type_id (int, FK to Type table)
Type table
type_id(PK) | type_name
Now you are safe if the name of a class changes in the future (concern from your first proposition with string type). Indeed, all you would do is change the type_name
value in the corresponding Type
table row and all your Action
rows would still be linked to this row by the type_id
, which never changes once created (no problem here, as it does not hold any "business meaning").
And you have your hash-table from 3 (the Type
table) in a readable format as it is the RDMBS's responsibility to manage the keys of the hash-table (the type_id
PK).
Note that you won't have to tie your class to an int
value corresponding to the type_id
column, but rather fetch from the Type
table the type_id
by looking it up against the Class type (type_name
).
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…