一个真正的查找表优势与劣势 [英] One true Lookup table advantages vs. disadvantages

问题描述

所以我已经阅读足够了，知道有一个查找表不是最好的方法去。从性能的角度来看，为什么？请考虑以下示例：

So I've read enough to know that having one lookup table isn't the best way to go. From a performance standpoint, why is that? Consider the following example:

您有城市中的学校列表。我可以在给定的城市有一个以上的学校。
所以你有一个连接表schoolCities两者之间。现在考虑一个企业的列表。在每个城市可以有多个业务。所以你有一个连接表businessCities。问题真的是：如果你只是使用一个城市表，并为学校和商业做一个接合表，而不是每个表学校和企业自己的城市表的副本，这么糟糕为什么？

you have a list of schools in cities. There can me more than one school in a given city. So you have a junction table schoolCities between the two. Now consider a list of businesses. there can be more than one business in each city. So you have a junction table businessCities. The question really is: why is it so bad if you just use one cities table and make a junction table for schools and business versus having each table school and business its own copy of the cities table?

推荐答案

你所描述的不是一个真正的查找表;在你的例子中的城市表听起来像一个好的规范化的想法。 一个真正的查找表将更像

What you're describing isn't the "one true lookup table;" a Cities table in your example sounds like a good normalizing idea. The "one true lookup table" would be more like

CREATE TABLE TheOne (
    LookupType varchar(20) NOT NULL -- City, color, shoe size...?
    , LookupValue varchar(1000) NOT NULL -- how big does it have to be?
    , CONSTRAINT pk_TheOne PRIMARY KEY (LookupType, LookupValue)
)

，您可能有值：

'City', 'Philadelphia'
'City', 'St. Petersburg'
'City', 'Paris'
'State', 'Pennsylvania'
'State', 'Florida'
'Country', 'United States'
'Country', 'France'
'Brand', 'Lucky'
'Brand', 'Diesel'
'Brand', 'Old Navy'

等。纯粹的邪恶。

城市表可能会将城市与州或其他父级地理区域相关联，因为可能有许多不同的城市具有相同的名称（巴黎，利马，Levittown等）。

A Cities table would probably associate a city with a State or other parent geographical region, since there can be many different cities with the same name (Paris, Lima, Levittown, etc).

这篇关于一个真正的查找表优势与劣势的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！