关于幻数:数据库设计查找表

关于幻数:数据库设计查找表

Database Design Lookup tables

我目前正在尝试改进旧版数据库的设计,并且遇到以下情况

当前,我有一个表SalesLead,用于存储LeadSource。

1
2
3
4
5
Create Table SalesLead(
    ....
    LeadSource varchar(20)
    ....
)

潜在客户来源会很有帮助地存储在表格中。

1
2
3
4
Create Table LeadSource (
    LeadSourceId int,   /*the PK*/
    LeadSource varchar(20)
)

所以我只想从一个到另一个创建一个外键,并删除非标准化列。

我希望所有标准的东西。

这是我的问题。我似乎无法摆脱这个问题,而不是写

1
 SELECT * FROM SalesLead Where LeadSource = 'foo'

这完全是我现在要写的

1
SELECT * FROM SalesLead where FK_LeadSourceID = 1

1
2
3
SELECT * FROM SalesLead
INNER JOIN LeadSource ON SalesLead.FK_LeadSourceID = LeadSource.LeadSourceId
where LeadSource.LeadSource ="foo"

如果我们更改LeadSource字段的内容,则会中断。

在我的应用程序中,每当我想更改SalesLead的LeadSource的值时,我都不想将其从1更新为2(例如),因为我不想让开发人员不得不记住这些神奇的数字。这些id是任意的,应保持不变。

如何在我的应用程序代码中删除或否定对它们的依赖?

我的解决方案必须支持的编辑语言

  • .NET 2.0 3(对于asp.net,vb.net和c#具有什么价值)
  • VBA(访问)
  • 数据库(MSSQL 2000)

Edit 2.0联接很好,只是'foo'可以根据请求更改为'foobar',而且我不想拖延查询。


如果要对表进行非规范化,只需将LeadSource(Varchar)列添加到SalesLead表中,而不使用FK或ID。

另一方面,如果您的语言支持ENUM结构,则"魔术数字"应该安全地存储在枚举中,因此您可以:

1
SELECT * FROM SALESLEAD WHERE LeadSouce = (int) EnmLeadSource.Foo; //pseudocode

您的代码将带有一个

1
2
3
4
5
public enum EnmLeadSource
{
   Foo = 1,
   Bar = 2
}

可以消除一些过度的规范化,如果这样做给您带来的麻烦多于其修复的问题。但是,请记住,如果使用VARCHAR字段(与"幻数"相对),则必须保持一致性,如果需要多种语言或文化,以后可能很难进行本地化。

规范化之后最好的方法似乎是使用Enum结构。它使代码保持整洁,并且您始终可以在方法和函数之间传递枚举。 (我在这里假设使用.NET,但也使用其他语言)。

更新:由于您使用的是.NET,因此,如果您要通过代码构造查询,则数据库后端为"无关"。想象一下这个函数:

1
2
3
4
public void GiveMeSalesLeadGiven( EnmLeadSource thisLeadSource )
{
  // Construct your string using the value of thisLeadSource
}

在表中,您将有一个LeadSource(INT)列。但是,它具有1,2或N的事实对您而言并不重要。如果以后需要将foo更改为foobar,则可能意味着:

1)所有"数字1 "必须为数字" 2 "。您将不得不更新表格。
2)或者您现在需要Foo成为2号和Bar号1号。您只需更改Enum(但要确保表值保持一致)。

如果正确使用,枚举是一个非常有用的结构。

希望这会有所帮助。


您是否考虑过不对LeadSource表使用人工键?然后,您可以使用LeadSource作为SalesLead中的FK,这可以简化查询,同时保留使用规范的一组值(LeadSource中的行)的好处。


您是否考虑过可更新的观点?根据数据库服务器和数据库设计的完整性,您将能够创建一个视图,当其值更改时,该视图将依次更新组成表。


这里有一个错误的二分法。

1
2
3
SELECT * FROM SalesLead
INNER JOIN LeadSource ON SalesLead.FK_LeadSourceID = LeadSource.LeadSourceId
where LeadSource.LeadSource ="foo"

没有破损,只有原始的

1
SELECT * FROM SalesLead Where LeadSource = 'foo'

foo更改为foobar时的

。另外,如果您正在使用参数化查询(确实应该使用),则当foo更改为foobar时,无需更改任何内容。


在典型的应用程序中,将向用户显示潜在顾客来源列表(通过查询LeadSource表返回),随后应用程序将根据用户的选择动态创建后续的SalesLead查询。

您的应用程序似乎具有一些"知名的"潜在客户源,您需要为其编写特定的查询。如果是这种情况,请在LeadSource表中添加第三个(唯一)字段,其中包含一个不变的\\'name \\',您可以将其用作应用程序查询的基础。

这将魔术的负担从数据库生成的魔术编号(可能因安装而异)转移到系统定义的魔术名称(由设计确定)。


如果您通过引入新的关系/表来"改善设计",那么您肯定会需要不同的实体。如果是这样,您将需要处理它们的语义。

在先前的解决方案中,您可以将LeadSource名称更新为所需的相应SalesLead行中的名称。如果在新结构中更新名称,则对所有SalesLead行进行更新。

没有办法解决这些不同的语义。您只需要这样做。为了使表更易于查询,您可以使用已经建议的视图,但是我希望它们主要用于报告目的或向后兼容,前提是它们不可更新,因为更新此视图的每个人都不会知道已更改语义。

如果您不喜欢加入,请尝试
SELECT * FROM SalesLead,其中LeadSourceId IN(从LeadSource WHERE LeadSource = \\'foo \\'中选择ID)


我真的看不到联接后面的问题。

自然地,直接由FK_LeadSourceID询问是错误的,但是使用JOIN似乎是正确的方法,因为我完全掩盖了更改ID的正确性。例如,如果" foo "在一天中变为3(并且您更新了外键字段),则您显示的最后一个查询将仍然完全相同。

如果要在不更改应用程序中当前查询的情况下更改架构,则可以使用包含此联接的视图。

或者如果您担心联接语法不直观,那么总是有子选择...

1
2
SELECT * FROM SalesLead where FK_LeadSourceID =
         (SELECT LeadSourceID from LeadSource WHERE LeadSource = 'foo')

但请记住在LeadSource.LeadSource上保留一个索引-至少如果表中存储了很多索引。


推荐阅读