1

All,

I have read several other posts before posing my question to you.

I have a lot of programming/admin experience with other databases: MySql, MSSQL, PostGres, and the one which will not be named. I just don't have much experience with Oracle.

I was tasked with designing a few web-applications and supporting database tables. The tables were designed using an ER diagram, and sent to the development group for implementation. When they sent back the proposed table creation statements, I saw two things that seems wrong to me. The primary key is NUMBER(5) and the sequence set the MAXVALUE to 99999.

I would have expected that the MAXVALUE would be omitted in favor of NOMAXVALUE Primary key column be a NUMBER(*,0) or a LONG. Since I don't have much experience with Oracle table design, would you please offer up your advice?

Sincerely

Kristofer Hoch

Edit Thank you for the information on LONG. I'll make sure to use NUMBER, but I'm still unclear on the best way to define it: NUMBER, or NUMBER(*,0), or NUMBER(9), etc.

Community
  • 1
  • 1
Kristofer Hoch
  • 246
  • 1
  • 7
  • How many rows do you expect to be inserted in the lifespan of the said table? – Raihan Dec 14 '11 at 16:28
  • COuld be 10s of thousands or more. Its part of an enterprise solution for a global energy company. – Kristofer Hoch Dec 14 '11 at 16:39
  • Then definitely `number(5)` does not suffice as the size of the primary key. Apparently the person writing the table creation scripts had no idea about the volumn of data. – Raihan Dec 14 '11 at 16:44

2 Answers2

1

I agree with you: since the column is to hold a surrogate key generated from a sequence, the only possible purpose of the 5 digit limit would be to restrict the total number of rows ever allowed in the table to under 100,000 - which would seem perverse. It certainly does not confer any performance or space efficiency advantages. Probably it is just the default of their ERD tool's DDL generator.

Do not use LONG: in Oracle that is an obsolete and deprecated way of storing large text strings (for which CLOB is now preferred).

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
1

If it was me, I wouldn't specify the size of the number, but that may be more laziness (set it, forget it) than a good practice for design purposes.

But "long" would cause problems. In Oracle, "long" is a character data type that's being deprecated. It's not the same thing as the long data type for numbers in other languages/systems. It's tricky.

l_smith
  • 123
  • 1
  • 6