Question: What are the adverse effects of having too many lookup tables in the database?

I have to incorportate too many Enumerations, based on the applications. 

What would experts advice?

Answer: 
As Florian, I like a lot more to have tons of Foreign Keys then to have CHECK IN (..) - for a simple reason: you can insert other records on your tables.

Maintaning CHECK IN () is a much bigger problem. Imagine this scenario:

```
CREATE TABLE street
(
   id serial not null,
   st_type varchar(20) not null,
   st_name varchar(100) not null,
   constraint street_pk primary key (id)
   constraint street_type_check check st_type in ('STREET','AVENUE','SQUARE')
);

```

You have 1000 rows with those types checked, correct? If you need to add another one, you will need to drop the constraint and recreate it.

IF you take a item off that list, like SQUARE, what will happen to the rows already commited (and checked at moment of insertion) that have that type? They will still keep an invalid type.

Tables and Foreign Keys are easier to maintain and keep track of.
The Whole point of lookup data is that there is a finite list of valid identifiers for a specific field. if those specific fields are used in procedures or where statements to determine the correct process path or the limit the select list, then there is no such thing as too many lookups.

if it is not a finite list of identifiers for a specific process or where clause then they should not be a lookup value.

two types of fields that come to mind which might be considered lookup values but don't necessarily need to be.

City and Province/state:

There is a finite list of these but because there are sooo many you might not want to make a lookup for these.