For type/status fields in the past, I've tended to just store them as
attributes in the table itself instead of have a lookup (with a few
exceptions like US State, etc). To me, this made it easier to do ad
hoc queries, see the value in the table when using a tool like DBEdit,
and I figured the storage was equal-to or less-than the size of the FK
to access the reference table. Of course, eliminating a join would
also improve performance. That's just been my preference -- trying to
find a balance between practical and ideal.
I'm not sure if this example will help you, either. It worked very
well for me in the past. I'd like to get it working for Cayenne 3.0,
too (and maybe integrate into core Cayenne?).
http://cwiki.apache.org/CAY/enumerations-example.html
The advantages of the way I did it is it provided strongly typed
setters/getters, you could map to arbitrary values in the DB (strings
and integers -- current Cayenne 3.0 enum support only supports the
position of the enum, starting at 0), you could use the enum constant
in your queries, the enum values would be translated in the SQL logs.
/dev/mrg
On Jan 16, 2008 10:43 AM, Kevin Menard <kmenar..ervprise.com> wrote:
> Hi all,
>
> I'm looking at a problem that enums look like a good fit for, but I've never
> bothered with Cayenne's enum support. So, I'm looking for some help on best
> practices.
>
> What I have is an "orders" table that I would like to store an "eval_status"
> value to indicate whether the order is as an evaluation order and at what
> stage of the eval it is.
>
> Normally, I decompose everything and would have an "eval_status" table with
> various values and set up an FK constraint on an "eval_status_id" column on
> the "orders" table. I don't expect these values to change, but if they do,
> I'd like to be able to do a simple UPDATE statement on the one row.
>
> In my code, however, I think it would be really nice to be able to use
> expressions such as: if (EvalStatus.CONVERTED == order.getEvalStatus()). It
> seems that this would be much nicer than using a Cayenne DO for the
> comparison.
>
> Despite all of this, I don't want to be binding the data model too tightly
> to Java. In the past we've used Python to access the DB, so I'd like to
> support that as well as I can.
>
> The best way I've come up with is if the enum maps its values to the PKs of
> the entries in "eval_status" table. I think this would achieve what I'd
> like, with the downside that referential integrity may be compromised if I
> update one but not the other.
>
> Is this largely what others are doing? Or, do you just use VARCHAR columns
> and not worry about the normalization of the DB?
>
> Thanks,
> Kevin
>
>
This archive was generated by hypermail 2.0.0 : Wed Jan 16 2008 - 11:24:09 EST