Using enum vs. check constraint vs. lookup tables

Say I have a bunch of fairly static lists that I want to be able to select from… competitor classification, equipment category, etc. - stuff that isn’t likely to change very often, if at all.

What would be the pros n cons of using the above alternatives to storing that info in a database?

Would it make any meaningful difference if the database was PostgreSQL instead of MySQL i.e. one way being more ‘portable’?

Thanks,

Monte

enums and check constraints require DBA involvement to make changes, whereas adding a new entry to a lookup table falls within user purview like any other user data

yes it would make a difference which database system you use, as they handle these things differently

mysql, for example, does not support check constraints, and the enum syntax in postgresql is different (other database systems like ms sqlserver don’t support enums at all)

~all~ database systems support tables, though :slight_smile:

Rudy,

Thanks for the response. Is there any difference in speed between these options? For the most part what I’m looking at right now is mostly low-volume i.e. only a couple users and (optimistically) a few thousand records overall. When I’ve been sketching things out it just seemed awkward to have a handful of very small tables scattered around my ERD, most with a single column and between five and ten records that will almost never change. Then when I started reading about ENUM, it sounded like the perfect solution… until I found out that it isn’t really implemented the same way across different databases (MySQL vs. PostgreSQL), but a check constraint is supposed to be more standardized SQL… other than MySQL still ignores check constraints (why is that?!?) which leaves lookup tables as the only ‘universal’ solution - if I decide I really want/need things to be that portable. Or am I agonizing over nothing :wink: and should just pick one and run with it?

Thanks,

Monte

no, you are not, and yes, you should

my advice would be the lookup tables

and don’t make the mistake of trying to generalize them into a single table – do a search for OTLT (“one true lookup table”) for further horror stories

Cool… That ‘OTLT’ subject looks to be keepin’ me busy reading for a bit :wink:

Let me ask this another way, if you don’t mind…

IF MySQL actually processed standard SQL check constraints like other RDBMS… or if not using MySQL at all, would you still prefer lookup tables? Is it more a matter of portability i.e. every DB understands the SQL for lookup tables whereas enum is subject to local interpretation and MySQL ignores check constraints, or is it that much easier to add a value to a small lookup table with an insert statement as opposed to an alter table command to update the check constraint parameters? For the values I have in mind this would not be a user-changeable list so in either event it would have to be done by the person maintaining the database/application…

TIA,

Monte

i still prefer the lookup tables

and i don’t call them lookup tables – just tables

you’d be surprised how often an additional attribute is discovered that can then ~only~ be handled by a column in the table

for example, suppose you are storing products as your main data, and you need a product type

so let’s say you declare a check constraint that looks something like


CREATE TABLE products
( pcode VARCHAR(15) NOT NULL PRIMARY KEY
, pdescr VARCHAR(255) NOT NULL
, pprice DECIMAL(7,2) NOT NULL
, ptype CHAR(2) NOT NULL
, CONSTRAINT valid_product_type
     CHECK ( ptype IN 'AA','AB','DD','EE' )
, ... )

so you have four different types of product, which you’ve declared with either an ENUM (i hate enums anyway so this is the least attractive option), or with a CHECK constraint as above

now all of a sudden you need to modify the app slightly – all AB products are henceforth going to get a 10% discount

how do you do that with a CHECK constraint? you can’t, you end up hardcoding what is actually data into your app logic –

if ptype = 'AB'
  then itemprice = pprice * 0.90
  else itemprice = pprice

however, if product types are declared in their own table, then you would have


CREATE TABLE products
( pcode VARCHAR(15) NOT NULL PRIMARY KEY
, pdescr VARCHAR(255) NOT NULL
, pprice DECIMAL(7,2) NOT NULL
, ptype CHAR(2) NOT NULL
, CONSTRAINT valid_product_type
     FOREIGN KEY ( ptype ) REFERENCES product_types ( ptype )
, ... )

CREATE TABLE product_types
( ptype CHAR(2) NOT NULL PRIMARY KEY
, pdisc TINYINT NOT NULL
)
INSERT INTO product_types VALUES
  ( 'AA' , 0)
, ( 'AB' , 10 )
, ( 'DD' , 0 )
, ( 'EE' , 0 )

and then your application logic looks like


itemprice = pprice * ( 100 - pdisc )

vwalah

:slight_smile: