什么时候需要在数据库中添加一个表来查找值 [英] When is it necessary to add a table in the database for lookup values

查看:24
本文介绍了什么时候需要在数据库中添加一个表来查找值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

只是寻找一些关于何时使用查找值表的一般方向/最佳实践.假设我有一个包含交易/付款的表格,其中包含以下列:

just looking for some general direction/best practice about when to use a lookup value table. Lets say I have a table with transactions/payments in it which has the following columns:

交易

  • transaction_id
  • order_id
  • amount_paid
  • 付款状态
  • 付款类型

好的,可以说 payment_type 可以是 3 个选项之一(信用卡"、直接信用卡"或国际汇款").目前,对于每个表行,此 payment_type 字段都存储为文本.现在是将此值存储为文本还是为其创建一个查找表并将外键存储回事务表中更好?

Ok lets say payment_type can be one of 3 options ('credit card', 'direct credit', or 'international money transfer'). Currently for every table row this payment_type field is being stored as text. Now is it better to store this value as text or make a lookup table for it and store the foreign key back in the transactions table?

例如

payment_type

  • payment_type_id
  • payment_type_name

交易

  • transaction_id
  • order_id
  • amount_paid
  • 付款状态
  • payment_type_id

现在我的想法是这个领域与 Web 应用程序紧密耦合,所以如果将来添加更多支付类型,应用程序可能需要更改以适应它.添加另一种付款类型与文本一样容易,例如'paypal' 或在查找表中添加另一个条目.各种支付类型可以以数组的形式存储在网站代码中.

Now my thoughts are this field is tightly coupled with the web application, so if more payment types are added in the future, the application will likely have to change to accommodate it. It would be just as easy to add another payment type as text e.g. 'paypal' or add another entry into the lookup table. The various payment types could be stored within the website code in an array.

我可以看到使用查找表的好处是数据库中的整体数据较少(存储键而不是文本).缺点可能是查询速度稍慢,因为它必须对数据进行另一个连接?

The benefits I can see of using the lookup table are less overall data in the database (storing a key rather than text). Disadvantages are maybe slightly slower querying as it has to do another join on the data?

那么这里的最佳做法是什么?

So what's the best practice here?

推荐答案

是必要的...

我想说在这种情况下最好使用查找表.

I'd say that in this case it is best practice to have the lookup table.

如果您有一个简单的活动/非活动或是/否列,您可以只使用 char(1) 和检查约束或一点,但如果您表示任何更复杂的内容,最好有一个查找表.

if you have a simple active/inactive or Yes/No column you can just use a char(1) and a check constraint or a bit, but it is best to have a lookup table if you are representing anything more complex.

然后您可以将此表用于用户表单输入(填充选择框等)

You can then use this table for the user form input (populating the select box, etc.)

这将展平和缩小列,这将允许每页更多行,并有助于缓存主表的内存使用.

this will flatten and shrink the column, which will allow more rows per page, and help cache memory usage of the main table.

这篇关于什么时候需要在数据库中添加一个表来查找值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆