Suggestions for refactoring unusual tables

Hello.

I'm looking at a medium/large web application with 72 tables on a
company intranet. Two of the tables are unusual; they appear to have
been created to avoid having too many additional (very small) tables in
the database. The rough structure of these tables is like this:

Table "catalog":
----------------
id (int, primary key)
systext (string)
dshort (string)
dlong (string)

Table "catalog_entry":
----------------------
id (int, primary key)
catalog_id (int, references catalog)
systext (string)
dshort (string)
dlong (string)
rank (int)

"systext" is an alternate unique human-readable identifier for the
record, so that it can be referenced by name from SQL queries.
"dshort" and "dlong" are short and long labels, respectively.
"rank" is used to determine in which order the catalog_entry records
should be displayed.

Most of the other tables in the schema reference catalog_entry, for
various purposes (such as classification, current status, etc).
For example:

Table "contact":
----------------
id (int, primary key)
name (string)
....
type_id (int, references catalog_entry)
language_id (int, references catalog_entry)
billing_type_id (int, references catalog_entry

When the users edit a record, they usually see a dropdown box to select
one of the entries in a catalog. That's where the "dshort", "dlong", and
"rank" fields are used.

There are 63 catalogs. Only 6 of them contain 20+ entries (max. is 57),
half of them contain 5 entries or less (min. is 2). Here is some example
data from catalog_entry:

SELECT cat.systext, entry.systext
FROM catalog cat
JOIN catalog_entry entry ON entry.catalog_id = cat.id
WHERE cat.systext IN ('coverage', 'document_type');

cat.systext | entry.systext
---------------+------------------------
coverage | international
coverage | national
coverage | regional
document_type | notice_of_registration
document_type | reminder_1
document_type | reminder_2
( ... 25 more results ... )
document_type | publication_invoice
document_type | registragion_expired

My first thought was this looks unhealthy, and that every catalog should
be a separate table. That way the referencial integrity checks could be
enforced by the database - as it is, it would not be a foreign key
violation if the "language_id" value from the "contact" table pointed to
a catalog_entry that is actually part of the "document_type" catalog.

However, I hesitate to break this up, not just because that would almost
double the number of tables, or because it would mean a lot of
restructuring, but also because most of these new tables would be *very*
small (5 rows or less), and they would all have the same columns.

To make matters worse, catalogs can (optionally) be organized
hierarchically - some catalogs have one or more parent catalogs (there
is a separate table for this n:m relationship).

I'm not sure how to approach this. Is that part of the database design
"good enough" to be left alone, or should I break it up? If so, is it
really usual or desirable to have lots of mini-tables that all look
alike, and only have 2-5 rows each?
On the other hand, I suppose I could solve the current weakness
concerning referential integrity with column constraints, so that
shouldn't be a problem.

Any suggestions or hints would be very appreciated.


TIA,
stefan
.

Relevant Pages
Loading