Question about datatypes

Giot some developers here using Sybase's Power Designer to model their DB. (I have little input by the way folks).

They have a multitude of datatypes in their model, and for the life of me I can't see why you would use them in an Oracle DB. Well, perhaps I should say I can't see why you would implemenet this model directly into an Oracle table.

There are data domains mapped to proposed datatypes like:

Domain Datatype
-------- -----------
CURRENCY NUMBER(9,2)
LCURRENCY NUMBER(12,2)(A bigger currency amount!)
QTY NUMBER(11,3)
LQTY NUMBER(14,3) (A much bigger qty!)
CODE VARCHAR2(10)
SCODE VARCHAR2(2) (A small code!)

For the life of me I can't see why - other than to enforce some business rule at the table definition level - why you'd implement domains like this.

Any comments - opinions?

I'm not sure I'm following the question, but that's never stopped me from throwing out an answer before :)

Are you asking why anyone would restrict the size of any given field? Personally, I don't restrict anywhere I don't have to. I like having fields that are just NUMBER with no restrictions, but that's just my style. I don't like arbitrary restrictions, but many people do. Oracle obviously does. They make you set restrictions on every single variable in the whole bloody database. So lots of people set maximum sizes for every single field - it's a fairly standard practice. What's the problem?

Are you asking why anyone would come up with a standardized set of datatypes and sizes? Well, whether your standard is to set limits on every field or not, enforcing your set of standards is always a good idea. Consistency is more important than accuracy, as they say. So what's wrong with saying there are 2 standard currency definitions that we like and here they are? What's the problem?

As far as enforcing a business rule at the table definition level - don't kid yourself - EVERYTHING in the database is the implementation of a business rule. Now, the database should strive to be larger and more generic than the application that it is first written with, because databases live longer than mere applications. Regardless, however, they exist for the sole purpose of satisfying a business need, and therefore exist completely within a world of business rules. So, if the rule is that no quanity shall ever be larger than 999, then so be it. You can argue against such an arbitrary limitation, and I would, but in the end, the database serves the business rules. So yeah - they might be database implementations of business rules. Again, what's the problem?

As far as Power Designer goes - it's a very nice product. I haven't used it in several years, but I remember it being a lot better than ErWin in the usability department. Of course, once I got into ErWin's macro language-capabilities, I never looked back - they are incredibly powerful (although very poorly implemented). ErWin has a whole host of usability issues that they seem truly incapable of solving, and most of which Power Designer handles very nicely. Having said that, both products are still orders of magnitude better than Oracle's POS designing product, from what I've seen. So, once again, what's the problem?

Help me out. What am I missing?

- Chris

You aint missing anything, Chris. Good reply.

I take your point about business rules and agree with it all - maybe I wasn't exactly clear in my question.

What wouldn't they just declare a currency datatype of, say, NUMBER(25,2) and have done with it. It could then accept both of their own CURRENCY and LCURRENCY datatypes, and have room to grow should the business rules change in the future. Are they relying on the database to prevent their application from inserting too large a value into the column? Is it a mnemonic definition to help developers to scale their application interface accordingly?

I can't see any storage or usability features of restricting the definition of 1 to (9,2) and another to (11,2) just because CURRENCY will always be a smaller amount than LCURRENCY. Why not just make them both (11,2)?

Maybe I'm confusing myself here...

Quote:

---

Originally posted by chrisrlong
EVERYTHING in the database is the implementation of a business rule. Now, the database should strive to be larger and more generic than the application that it is first written with, because databases live longer than mere applications.

---

. . . db's can even out-live the business (or at least the type of business that defined the rules). The question is: if you enforce the rules like this, how easily can you change them? Luckily ALTER TABLE exists . . .

Short war story - Pharma industry 1980's:
Unit prices were typically $10 - but could be billed in foreign currency and prices do rise - so max unit price was set at 999'999.99 Fine, until we moved into counter-trade and sold a single piece of machinery for several billions of Italian Lira.

Lucky it wasn't Turkish Lira.
A taxi ride in Istanbul once cost me about 4,000,000 lira.

So it was the original, 'why put sizes on at all?' question.

And I agree, I fight against arbitrary limitations every chance I get. As far as the database is concerned, they are mostly unimportant. Now, the application may see this in a different light. From the front-end, the user interface often can only allow so much data to be entered and displayed, as real-estate is the precious commodity for the UI. Then, the code must store these values in memory, which is the precious commodity for the middle tier, so things are often kept as tightly as possible. Then they store it in the database, where we don't care, but they don't want to take the chance that:

- The database can store any size value.
- So some data gets in there through some other source that is too large.
- They then read it back into the application and break a variable size there.
- Or it makes it to the user interface where it can't be displayed and it either breaks or looks bad.

So people often pick arbitrary sizes that they want enforced everywhere. I don't know that there is a way around it.

So if there need to be arbitrary sizes, then grouping them into a set of domains so they can be better controlled is a good idea, IMHO.

Having said all that, one must admit that, as I mentioned before, consistency is often better than accuracy. Even if I don't care about sizes for many reasons, there are some where I do. In the database, one is concerned with 'average' sizes. One needs to know what the average record size will be so one can size the table attributes correctly - PCTFREE, PCTUSED, etc. Setting arbitrary field sizes allows one to calculate a 'maximum' record size as well. If every string field were defined at 4K, for example, your sizing calculations might not be very accurate. The fluctuations could be too extreme, etc.

Do they really need 4000 characters for their first name? Probably not. Do they really need to be able to put in an hourly rate of 10 billion dollars? Probably not. Can allowing such incredibly extreme values cause headaches? Probably so. So some restraints are often sensible, but where to draw the line is always a battle.

Good luck,

- Chris

(cripes but I can ramble)