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: 
Let me tell how awful it is to have too few lookup tables. THe orginal designers at one place I worked decided to put all lookups into one table and define what the lookups were for using a typeid. This caused almost all queries to hit this table to get the lookup descriptive value causing a performance jam. 

Further, without separate lookups, the fields that took the typeid were not constrained by the values appropriate to that field because a foreign key can only be on the the whole table not a chunk. So the filed that stored the clientid might accidentally contain the value for a user group. This caused data integrity problems and made reporting much more difficult as we had to intepret values that didn't make sense in context. There is no prize for using too few tables, in fact it is often an anti-pattern in database design. 

Create 1000 lookup tables if that is what you need.
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.