All geek questions in one place

JPA Lookup table design

I have a design dilemma in my hands, and I hope you guys can help me with this.

Problem: I have about 15 lookup tables for my application with code and value attributes. I was wondering what would be the best design for creating these lookup tables.

I had several decisions in my head

  • The presence of one abstract superclass (for example, BaseLookupTable) with code and property values, and then about creating 15 specialized search classes that extend this class. or
  • Create an enumeration type for each of the 15 lookup tables.

If you guys could help me choose a solution (even those that were not mentioned by me) along with the reasons for this, would be very grateful.

Cheers, Samant

+4
source share
2 answers

I would choose option 1, as listed in your code. If you want to add an option to your lookup table, you only need to insert it into your database. Using enumerations, you need to recompile the application.

+1
source

I do not think that the common base class JPA has such widespread use.

Assuming you are not modeling a common key / value interface, then why not encode the specific semantics into the column names of the lookup table, for example. role_name, status_code, etc.

Not sure if you are also using DB ENUM. MySQL ENUM . I assume this for the next paragraph (it is specific to MySQL). Other DBMSs, such as PostgreSQL, have redundant ENUM elements, so the following may not apply to your situation:

MySQL ENUM cannot be applied in any situation. You should use MySQL ENUM if the search values ​​are unique and basically only refer to one table / entity. My policy is to add a separate lookup table if the values ​​get a link from more than one other table, or if the lookup table needs more than one attribute. Note that using the same ENUM definition in a DB adds redundancy. Using MySQL ENUM saves table, join, and some relationships. Remember that DB ENUM values ​​should be as static as possible. If they need to change, use the lookup table again.

This is not an easy topic, and I had to share my opinion on this topic, although this includes the features of MySQL.

0
source

Source: https://habr.com/ru/post/1379985/

More articles:


All Articles