UniqueIdentifier的优点和缺点?? [英] UniqueIdentifier pros and cons ??
问题描述
大家好
i我正在建立一个SQL 2000数据库，它证明了一点
挑战，我的公司有多个地址，电话号码，
拥有矿场等以及合资企业所以也许你会得到我遇到的一些设计问题的
图片
我的疑问是关于一个主键身份，以及与身份数据类型或唯一标识符相关的
使用哪一个，
我是建立一个地址表的asesentiall持有所有多个
地址以及电话号码等，因此我希望每条记录都有一个独特的
标识非常重要。
我的观点是我将通过使用表格来运行违规错误
身份数据类型，我可以假设使用复合主键但是
可能会对性能产生影响，虽然thiis不会是一个很高的b
交易数据库。
任何人都知道有关每个身份的性能问题
解决方案，通过使用生成的16位标识符，将会有大量数据供DB验证。或者我什么都不担心？
任何观点都非常感谢
问候
格雷格
我有几个关于IDENTITY的咆哮，它不能是一个关键的
定义。你可以谷歌他们。
建立一个地址表以保存所有多个
地址以及电话号码等，所以我希望每个记录[原文如此]拥有一个独特的
标识是非常重要的。 <<
行不是记录。您尝试建模的地址已经具有唯一的身份;你想要一个唯一的标识符。你希望它在现实中可以验证
（物理定位器如专有的
自动编号设备不是）。
$ b $你做过研究了吗？您的行业是否像书业一样使用SAN（标准
地址编号）？您的托运人是否使用某种类型的地址编号
（很好地协调并让他们为您维护
数据）？ ZIP + 4是否足够好（如果你只处理
更大的商业地址，这通常有用）？你有没有看过散装邮件使用的
散列方案（你需要购买地址
擦洗软件，所以看看他们是否也有一个散列程序作为
选项）？等等
--CELKO--
======================= ====
请发布DDL，以便人们不必猜测你的键中的键，
约束，声明性参照完整性，数据类型等等。 />
架构是。
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
Joe Celko写道：
我有几个关于IDENTITY的咆哮，这不是一个关键的定义。你可以谷歌他们。
我找到两个，在：
http://www.intelligententerprise.com...celko1_1.jhtml
http://www.intelligententerprise.com/000626/celko.jhtml
您的这里的投诉似乎主要是关于在没有明确声明主键的情况下使用Identity
。如果你正确
将TableID列声明为主键，那么
问题是什么？
Bill
>>如果你正确地将TableID列声明为主键，那么问题是什么呢？ <<
验证和数据质量。
如果我在车辆表中使用VIN作为钥匙，我可以验证通过
专业地查看引擎块。
如果我在零售商品表中使用UPC代码作为密钥，我可以验证
通过致电制造商（可靠来源）并阅读酒吧
代码。
这是多么普遍的，神奇的表格ID"？这是希伯来文中的一封17字母
字，上帝在宇宙开始时将创造的一切都置于创造的底部？你是用祷告来验证它的吗？
让我们试试这个用一个非常简单的桌子，将一个司机分配到一个
车辆的motorpool中。 br />
CREATE驱动程序
（id IDENTITY（1,1）NOT NULL PRIMARY KEY，
ssn CHAR（9）NOT NULL REFERENCES人员（ssn），
vin CHAR（17）NOT NULL参考文献Motorpool（vin））;
现在输入数据并提交相同的行一千次，百万美元b $ b次。您的数据完整性已被破坏。自然的关键是：
创建驱动程序
（ssn CHAR（9）NOT NULL参考人员（ssn），
vin CHAR（17）NOT NULL参考文献Motorpool（vin），
PRIMARY KEY（ssn，vin））;
为了演示，这里是一个典型的id新手模式 - 你会在新闻组中获得
。我称之为id-iots因为他们总是
将IDENTITY属性列命名为id在每张桌子里。他们不会理解基本数据建模 - 属性的唯一名称。
大约一半的时间他们不使用任何DRI，但让'显示它。
CREATE TABLE MotorPool
（id IDENTITY（1,1）NOT NULL PRIMARY KEY，
ssn CHAR（9）NOT NULL REFERENCES Personnel（id），
vin CHAR（17）NOT NULL REFERENCES Vehicle（id））;
CREATE TABLE Personnel
（id IDENTITY（1,1）NOT NULL PRIMARY KEY，
ssn CHAR（9）NOT NULL UNIQUE，
..）;
CREATE TABLE车辆
（id IDENTITY（1,1）NOT NOT PRIMARY KEY，
vin CHAR（17）NOT NULL UNIQUE，
..）;
现在更改人员中的自然键：
UPDATE Personnel
SET ssn =''666666666''
WHERE ssn =''000000000'';
Motorpool没有发生任何事情，它？你可以用
VIN做同样的事情。
现在你真的想到关系和关键而不是1950年代的
顺序记录编号。将IDENTITY列添加到
中的任何一个作为候选键的这些表将是危险的冗余;一个
查询使用IDENTITY而另一个使用真实钥匙，就像一个男人
有两只手表，你永远不知道它是什么时候。
最后，请求权威，Codd博士引用：..数据库
用户可能会导致系统生成或删除代理人，但他们
无法控制其价值，它的价值也没有显示给他们
......（COD博士，ACM TODS，pp 409-410）和Codd ，E。（1979），扩展
数据库关系模型以捕获更多含义。 ACM交易
数据库系统，4（4）。 pp.397-434。
这意味着代理人应该像指数一样行事;由
用户创建，由系统管理，绝不会被用户看到。这意味着在查询，DRI或用户所做的任何事情中都不会使用
。
Codd还写了以下内容：
"使用用户控制的密钥有三个困难，因为
实体的永久代理人。
（1）用户的实际价值控制密钥由用户确定
因此必须由他们更改（例如，如果两家公司合并，则两个员工数据库可能会与结果相结合
部分或全部序列号可能会被更改。）。
（2）两个关系可能在不同的
域名（例如一个使用社会保障，而另一个使用员工
序列号）但表示的实体是相同的。
（3）在分配用户控制的密钥值之前或之后，可能需要携带有关实体的信息
。 />
不再拥有一个（例如工作和退休人员的申请人。
这些困难的重要后果是，公共密钥值的公平加入可能不会产生相同的结果结果作为常见的
实体的连接。一个解决方案 - 在[4]部分[...]中提出并在[14]中更全面地提出） - 引入包含系统指定代理的实体域。
数据库用户可能会导致生成或删除代理人的系统，
但他们无法控制其价值，也没有显示其价值
给他们..... （Codd in ACM TODS，pp 409-410）。
参考文献
Codd，E。（1979），扩展数据库关系模型捕获更多
的含义。 ACM数据库系统事务，4（4）。 pp.397-434
--CELKO--
==================== =======
请发布DDL，以便人们不必猜测键，
约束，声明性参照完整性，数据类型等等你的
架构是。
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ......获得奖励！
Hi all
i am building a SQL 2000 database that it is proving a little
challenging, i have companies with multiple addresses, phone numbers,
owning mine sites etc and also joint ventures so maybe you get the
picture with a few design issues that i ma encountering
My queriy is about a primary key identity, and which one to use with
respect to either the identity data type or the unique identifier ,
I am aessentiall building an address table to hold all multiple
addresses as well as phone numbers etc, so my desire to have a unique
identity for each record is very important.
My view is i will run in to violation errors by just using the table
identity data type, i could i suppose use composit primary keys but
that may have a performance impact, although thiis will not be a high
transaction database.
Does anyone know about performance issues regarding each identity
solution, by using a generated 16 bit identifier there are going to be
huge numbers for the DB to verify. or am i worried about nothing?
any views greatly appreciated
regards
Greg
I have several rants about IDENTITY, which cannot be a key be
definition. You can Google them.
building an address table to hold all multiple
addresses as well as phone numbers etc, so my desire to have a unique
identity for each record [sic] is very important. <<
Rows are not records. The addresses you are trying to model already
have a unique identity; you want a unique identifier. You want it to be
verifiable in the reality (which physical locators like proprietary
auto-numberings devices are not).
Have you done your research? Does your industry use a SAN (Standard
Address Number) like the book industry? Does your shipper use some kind
of Address Number (nice to be co-ordinated and let them maintain the
data for you)? Is ZIP+4 good enough for you (if you only deal with
larger commerical addresses, this often works)? Have you looked at
hashing schemes used by bulk mailers (you will need to buy adddress
scrubbing software, so see if they also have a hashing routine as an
option)? Etc.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
Joe Celko wrote:
I have several rants about IDENTITY, which cannot be a key be
definition. You can Google them.
I found two, at:
http://www.intelligententerprise.com...celko1_1.jhtml
http://www.intelligententerprise.com/000626/celko.jhtml
Your complaints here seem to be mostly about using Identity
without explicitly declaring a primary key. If you properly
declare the TableID column as a primary key, what''s the
problem?
Bill
>> If you properly declare the TableID column as a primary key, what''s
the problem? <<
Verification and data quality.
If I use a VIN for the key in a table of vehicles, I can verify by
phsycially looking at the engine block.
If I use a UPC code for the key in a table of retail goods, I can verify
it by calling the manufacturer (trusted source) and reading the bar
code.
What the heck is this universal, magic "TableID"? Is it a 17 letter
word in Hebrew which God put on the bottom of everything in creation at
the beginning of the Universe? Do you verify it with prayer?
Let''s try this with a very simple table that assigns one driver to one
vehicle in the motorpool.
CREATE Drivers
(id IDENTITY (1,1) NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin));
Now input data and submit the same row a thousand times, a million
times. Your data integrity is trashed. The natural key was this:
CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin),
PRIMARY KEY (ssn, vin));
To demonstrate, here is a typical id-iot newbie schema -- you will them
all over the news groups. I call them "id-iots" because they always
name the IDENTITY property column "id" in EVERY table. They don''t
understand basic data modeling -- one and only name for an attribute.
About half the time they don''t use any DRI, but let''s show it.
CREATE TABLE MotorPool
(id IDENTITY (1,1) NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL REFERENCES Personnel(id),
vin CHAR(17) NOT NULL REFERENCES Vehicle(id));
CREATE TABLE Personnel
(id IDENTITY (1,1) NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL UNIQUE,
..);
CREATE TABLE Vehicles
(id IDENTITY (1,1) NOT NULL PRIMARY KEY,
vin CHAR(17) NOT NULL UNIQUE,
..);
Now change the natural key in Personnel:
UPDATE Personnel
SET ssn = ''666666666''
WHERE ssn = ''000000000'';
Nothing happened in Motorpool, did it? You can do the same thing with a
VIN.
Now you are REALLY thinking about relations and keys instead of 1950''s
sequential record numbering. Adding an IDENTITY column to either of
these tables as a candidate key would be dangerously redundant; one
query uses the IDENTITY and another uses the real key, and like a man
with two watches, you are never sure what time it is.
Finally, an appeal to authority, with a quote from Dr. Codd: "..Database
users may cause the system to generate or delete a surrogate, but they
have no control over its value, nor is its value ever displayed to them
..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending
the database relational model to capture more meaning. ACM Transactions
on Database Systems, 4(4). pp. 397-434.
This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means never
used in queries, DRI or anything else that a user does.
Codd also wrote the following:
"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.
(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two companies
merge, the two employee databases might be combined with the result that
some or all of the serial numbers might be changed.).
(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.
(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it has
ceased to have one (e.g. and applicant for a job and a retiree).
These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] - is
to introduce entity domains which contain system-assigned surrogates.
Database users may cause the system to generate or delete a surrogate,
but they have no control over its value, nor is its value ever displayed
to them....." (Codd in ACM TODS, pp 409-410).
References
Codd, E. (1979), Extending the database relational model to capture more
meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
这篇关于UniqueIdentifier的优点和缺点??的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
