关于数据库设计最佳实践/标准的建议 - 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?
- Whats the recommended data type for storing currencies (no financial operations, just displaying).
- Recommended size for storing phone numbers (internationals)
- Recommended minimum size for storing first names / last names (minimum meaning smallest maximum recommended size)
- Recommended minimum size for storing comment blocks.(minimum meaning smallest maximum recommended size also)
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
存储货币的推荐数据类型是什么
这取决于货币类型以及准确度。
如果是美分和美元，四舍五入到最接近的美分，则为
NUMBER(12,2)，它允许您存储-999,999,999,999.99和999,999,999,999.99之间的金额> - 对于大多数货币来说这应该足够了。如果您需要存储利率计算等中间结果，则可能需要更高的精度，例如
NUMBER(15,5)。如果您使用的是津巴布韦元，也许您应该选择最大的
NUMBER:)存储电话号码（国际电话号码）的建议大小
VARCHAR2(30) 应该足够了。如果太长，您的用户将在其中输入各种垃圾数据。
建议存储名字/姓氏的最小大小/
建议存储注释块的最小大小
这些不适用，因为您使用的是 Oracle - 使用 VARCHAR2，因此您不必担心最小大小。您需要指定的只是最大尺寸。
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.99and999,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
NUMBERinstead :)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.
货币：
NUMBER(15,2)，实际上取决于您期望遇到的数字有多大。电话号码：
VARCHAR2(30)，如果它应该更大，请不要伤害我 - 本身不记得长度，只是 VARCHAR 允许灵活的格式化。如果使用 VARCHAR2，我不认为查看最小大小有什么意义。物理模型的关注点围绕数据库随着时间的推移将消耗多少空间（假设字段已满）。
注释块：
最大 VARCHAR2(4000)
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)
EDIFACT 通常使用 35 作为名称字段的大小，我复制该内容（并将其记录为基础）。较新的内容往往在 XML 中定义，通常不涉及字段长度定义。
或者，加拿大邮局建议否每个地址行超过 40 个字符。
请注意，这是字符而不是字节。调整大小应考虑多字节字符，但显然并非所有名称都是最大长度。我使用每个名字的十个字符作为大小估计的大致近似值，但在国家、种族等之间可能会有很大差异。
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.
我知道您要求注释块的最小大小，但对于大型自由文本区域，您应该考虑使用 CLOB 值。 Oracle 对于如何处理这些事情、如何存储数据等方面非常聪明。您永远不必担心大小。此外，您通常可以假装它们是 VARCHAR2 列，以便于操作。
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.