General Architecture with Code Tables?

Question

I've only worked at once place since graduating my CS degree. This is a pretty basic architecture question but i don't know any better since i've only worked at one place. Where i work we maintain a large number of code tables. For example say you have a Sales/Order system and you have an Order Status. We would maintain an order status table that would look like the following:

OrderStatus:

OrderStatusId(PK TinyInt)  OrderStatusDesc(VarChar(50))  
1                         Created  
2                         Submitted 
3                         Processing
4                         Canceled
5                         Verified
6                         Complete

We then create an enumeration off each code table. For example:

Public Enum TblSalesOrderStatus
      eosCreated = 1
      eosSubmitted = 2
      eosProcess = 3
      eosCanceled = 4
      eosVerified = 5
      eosComplete = 6
End Enum

Then in our code we have code like the following:

If OrderStatus = eosCreated OrElse OrderStatus = eosSubmitted Then
    ...do some work
End if

On every screen we create we have the id's stored in the controls (like comboboxes). I don't know something about this has always made me think its bad design. Maybe i'm wrong though. Especially when i started getting into REST design. i wanted to pass the Ids rather than descriptions. Of course this doesn't seem right since i'd be the only one i've ever seen pass those type of Ids in a REST service. So is this bad design?

Edit:
Trying to make thing a little clearer. Are Code Tables are only stored in two places: The Enum + The Database. When we need a new Id when email our database staff which creates a new code table value for us and emails us back the Id. When then put the new value into our enumerations. We never had problems with it getting out of sync but if we have needed either delete values or change them in the past (adding is easy) which has been pain because everything in the system had to be recompiled.

We have went down the route of trying to make things semi-dynamic (which doesn't work out in every case but helps in some). There are tons of examples but a simple example is we have a IsVisible flag in some of the code tables. If we ever want to obsolete or make one of the values not selectable then we set the IsVisible flag = 0. They prefer this compared to having to change code/compile/deploy. Thinking about it i think it would be preferable to have it in buisness logic and have test around it (which we don't :-( ) depending on your prospective.

Answer 1

I don't know if this is officially "good" or "bad", but I have worked for a company where we used (and they use currently) this approach, with some differences:

• we had one code table, and each row was identified (and indexed on) the ["code type", "code id"] pair;
• there was a "master type" that described the code types themselves, so it was easy to add a new code type: added one row to the "master" code type, and the rows of the actual codes;
• we had other columns in this table, so we could add external information to the codes, which was used by the actual code type handler.

It was not only useful for storing string values for code ids. For some types (data tables, GUI panels, forms, letters, ...) we put the actual handler Java class name into the code store record, and when referred to "table 42", we got an actual business logic object filled with data. Yes, Spring was not there at that time.

I think this is basically a good design, let us create a very modular system and quick prototyping. The only problem with this approach was that a part of the "system" was actually the content of a data table, which was not easy to keep in sync with the codes and app versions (we put code table dumps into the version control system, not a nice thing).

Therefore today I would not store this table in a database, but in a plain text file (json, xml, cfg, whatever), which is easy to diff(!!!) and can be stored, branched, etc. in the version control, directly linked to the sources, because their combination forms the actual deployment version of the application.

So in your case, I would also add this "master code store" containing all the other code types (if not exist yet) to centralize the type management, and perhaps would collect the many small tables into a single table, with a type column. With indexed queries, (or even: views that would make this change transparent to the existing codes!) there is practically no performance difference, but it is much easier to maintain one code table. Using the master type, you can even write a nice manager tool (as we did, and it felt good: the management of some low priority codes was delegated to the client administrators).

Also, the combo boxes don't have to "know" the actual code table, only the type id in the common table. Practically, one combo class is enough, adding a new type means using the same combo box with the new id (the int/enum conversion does not belong to the GUI element, but the control logic listening to the combo).

I would even consider to programmatically generate the enum source codes from the table. This is a repetitive task, and I think it is quite easy to make mistakes here, but not that hard to automatize. It would somehow "protect" the content of the code table as well: "touch it only if you are ready to explain why other coders have to refactor their code to use the modified enums..." :-)

About passing the ids: you are absolutely right!

Your business logic has to know that you have the value enum blahBlah in field AAA - this has a meaning in your context (the fact that field AAA uses code type 42 is also out of scope for your business logic, that is an external configuration info). The combo box is responsible for knowing that id 2 in type 42 should be displayed as "Blah blah blah", and when the user selects the string "Yippiee hoo!" it is id 6 in type 42, and should NOT know that id 6 means enum yippieeHoo for your business logic! A totally different set of responsibilities - and I have not yet mentioned language versions, what makes this even more evident ;-)