4

I have a design dilemma at my hands and am hoping you guys could help me out with this.

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

I had couple of solutions in my mind

  1. Having one abstract superclass (say BaseLookupTable) with the code and value properties and then go about creating the 15 specialized lookup classes extending this class. or
  2. Create enum type for each of the 15 lookup tables.

If you guys could help me pick a solution (even ones not mentioned by me) along with reasons for doing it would be much appreciated.

Cheers, Sumanth

JProgrammer
  • 321
  • 4
  • 15

2 Answers2

1

I would go for option 1, since enums are fixed in your code. If you want to add an option to your lookup-table you only have to insert it in your database. With enums you need to recompile your application.

Iwan Flameling
  • 11
  • 1
0

I don't think a common JPA base class has that much use.

Assuming you don't model a common key/value interface, then why not encode the concrete semantics into the lookup table column names, e.g. role_name, status_code, etc.?

Not sure if you're using DB ENUMs also, e.g. MySQL ENUMs. I assume this for the next paragraph (it's MySQL specific). Other DBMSs like PostgreSQL have non-redundant ENUMs, so the following might not really apply to your situation:

MySQL ENUMs cannot be applied in every situation anyway. You should use MySQL ENUMs if the lookup values are single-valued and are basically referenced from one table/entity only. My policy is to add a separate lookup table if the values get referenced from more than one other table or the lookup table needs more than one attribute. Note, that using the same ENUM definition in the DB adds redundancy. Using MySQL ENUMs saves you a table, a join, and some relationships. Keep in mind the DB ENUM's values should be as static as possible. If they need to change, use a lookup table again.

It's not an easy topic honestly and I had to share my view on the topic, even though this includes MySQL specifics.

Kawu
  • 13,647
  • 34
  • 123
  • 195