How Many Unique Data Types in a System?

Our main product is a fairly large financial system with several hundred tables in the database (and hence thousands of columns). 

While looking at refactoring the GUI for the system, I really can't find more then perhaps a hundred or so distinct data types, or entities for you OO guys (name, address, phone number, money, date, time, counters of various things we count, free text fields, etc). Please don't speculate that the database is badly denormalized (it is); but I wonder if others have ever counted how many data types you really need in a typical business application and whether you've ever tried to identify them all (or do you do what our lead designer did and just declare everything as an integer, float, datetime, or varchar)?

Tom
Saturday, February 7, 2004

The whole issue of datatypes is an absolute nightmare in most databases as far as I can see, particularly if you're trying to address complex data. Mostly, I think, this is because its not possible fully to map the constraints seen in conceptual and logical data models into the physical schema. For example, there is a world of difference between, say, the length of a pipe and its diameter, or between a speed and a velocity, but all four inevitably end up being mapped to NUMBER, which means its hellishly easy to relate them in circumstances which are meaningless.

Units of measure are another nightmare. There are circumstances in which you really do want to hold data values in their original units of measure (say, feet), but you don't want some clot comparing a length in feet to one in metres, or inches, or whatever (I think NASA learnt this lesson the hard way). The result of all this is that you end up having to do a lot of stuff in server (or even worse client side) code that should be in the data storage engine.

Going back to the original post. I reckon a couple of hundred distinct _atomic_ types (equate database domains) is probably about right for a non-technical (or rather a non-engineering) database, but there might well be many more _complex_ types (roughly equate weak entities).

Has anyone ever come across an RDBMS that operates directly from a logical schema? I know that there are a lot of ERD tools that purport automatically to create a physical schema from the logical, but this doesn't help the meaningless query problem.

David Roper
Saturday, February 7, 2004

  Recent Topics

  Fog Creek Home