How to design tables for data dictionary when usin

Question

I have a data dictionary table like this:

tbl_data_dictionary

filed

type code code_name

rows

sex 1 boy

sex 2 girl

country 1 American

country 2 China

I have a data table:

field

name sex_code country_code

rows

vivic 1 2

How to I use hibernate to map the code to code name? The one_to_many mapping settings seems not to fit my needs. Is the only way to separate the tbl_data_dictionary to tbl_sex and tbl_country? But, if I have lots of types of dictionaries, and in one type there aren't many rows, I feel it's a waste. Can hibernate map like this: sex_code=code and type="sex"?

Answer 1

Our team uses such common table for similar (plain) dictionaries. It is very inconvenient.

So, It is better to use this approach:

• For small dictionaries use enums. You can store ordinals for large enums, if you will not use the external SQL (for example, for reports, generated using SQL). For small enums, names can be stored.
• For large dictionaries use separate tables with corresponding entities. It is convenient to have @MappedSuperclass for the common fields.

An example

enum Sex {
  MALE, FEMALE, UNKNOWN
}

@MappedSuperclass
class Dictionary {

    @Id
    private Long code;

    @Column
    private String name;

}

@Entity
class Country extends Dictionary {

}

@Entity
class User {

    @Column
    @Enumerated(EnumType.STRING)
    private Sex sex;

    @ManyToOne
    private Country country;

}