CODEWITHSUNDEEP
oracle-databasedatabase-designstandards
Chepech
Chepech

Reputation: 5541

Advice on DB design Best Practices/Standard - Oracle

I'm designing the DB for a new app which is something I've done a thousand times, but in this occasion I suddenly start wondering on some aspects that I've never stopped before. Is there some standard/recommendation for the following things?

I'm aware that every application has its own particular requirements to consider, but I feel that there must be something more specific than gut feeling and common sense.

Help, as always, will be deeply appreciated.

Upvotes: 1

Views: 1695

Answers (4)

anon
anon

Reputation:

I know you were asking minimum size for comment blocks, but for large free-text areas you ought to consider using a CLOB value. Oracle is pretty smart about how these things are handled, how the data is stored, etc. You NEVER have to worry about size. In addition, you can usually pretend that they are VARCHAR2 columns for easy manipulation.

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Whats the recommended data type for storing currencies

This depends on what kind of currency, and to what degree of accuracy.

If it's cents and dollars, rounded to the nearest cent, it's NUMBER(12,2) which allows you to store amounts between -999,999,999,999.99 and 999,999,999,999.99 - which for most currencies should be enough.

If you need to store intermediate results from, say, interest rate calculations, you may need more precision, e.g. NUMBER(15,5).

If you're talking Zimbabwean dollars, perhaps you should choose the maximum NUMBER instead :)

Recommended size for storing phone numbers (internationals)

VARCHAR2(30) should be sufficient. If it's too long your users will enter all sorts of rubbish data in there.

Recommended minimum size for storing first names / last names / Recommended minimum size for storing comment blocks

These don't apply since you're in Oracle - use VARCHAR2, so you don't have to worry about minimum size. All you need to specify is the maximum size.

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

EDIFACT generally uses 35 as the size of a Name field and I'd copy that (and document that as a basis). Newer stuff tends to be defined in XML and doesn't normally go into field length definitions.

Alternatively the Canadian post office recommends no more than 40 characters per address line.

Note, that is characters and not bytes. Sizing should take into account multi-byte characters, but obviously not all names will be the maximum length. I've used ten characters per name as a broad approximation for sizing estimates but that could vary a lot between countries, ethnicities etc.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332531

Currencies:

NUMBER(15,2), really depends on how big the numbers are that you expect to run into.

Phone numbers:

VARCHAR2(30), please don't hurt me if it should be larger - can't remember the length per se just that VARCHAR allows flexibility for formatting.

I don't see the point of looking at the minimum size if using VARCHAR2. The concerns for the physical model revolve around how much space the database will consume over time, assuming fields are maxed out.

Comment blocks:

Maximum of VARCHAR2(4000)

Upvotes: 1

Related Questions