数据库设计问题：丑陋或参考完整性？ [英] Database design question: ugliness or referential integrity?

问题描述

大家好：


让我们说我正在设计一个数据库（Postgres 7.3），其中包含所有

电子邮件帐户的列表某个服务器：

CREATE TABLE电子邮件（

clienteid INT4，

direccion VARCHAR（512）PRIMARY KEY，

login varchar（128）NOT NULL，

密码VARCHAR（128），

dominio VARCHAR（256）

）;

PHB想要记录添加电子邮件帐户的时间，

技术人员做了它，什么时候删除了，什么时候我们必须重置它

密码等：

CREATE TABLE emails_log（

direccion varchar（512）引用电子邮件，

fecha date，

autor varchar（32），

texto varchar（1024）

）;


" texto"将是一个自由格式文本字段，解释已完成的工作。

现在，让我们假设一个电子邮件帐户被删除，并且六个月

后来另一个用户请求它，我们再次添加它。我们是否希望保留

旧版版本的审计线索。那个账户？ PHB说是。

这意味着我们不能将电子邮件地址用作主键。好的，我们

添加一个ID列到电子邮件 table并将其设为主键，

并将外键指向emails_log;到那个专栏。但现在我们

有两个选项，这是我的问题：


-In" emails"，direccion"列需要是唯一的...但仅限于

活动电子邮件地址（可能有5,10或20个死地址

称为lu *** @ domain2.com"，但此刻只有一个活着）。我们可以

添加一个有效的布尔列到电子邮件，并写一个自定义的

约束来检查这个条件，但我发现它很难看（当我看到另一个用户时，我看到了

类似的异议有一个类似的问题一些

时间之前）...

-...或者我们可以创建一个名为dead_emails的表，并添加到其中

我们删除的电子邮件地址（可能使用ON DELETE触发器）。

基本上，将已删除的电子邮件帐户存储在另一个表中......但是然后

我们在emails_log中丢失了参照完整性检查。


问题是：你会怎么做？ （我不是很喜欢

创建另一个指向dead_emails的dead_emails_log表;

我发现它几乎和第一个）。


Paulo Jan.

DDnet。


--------- ------------------（广播结束）---------------------------

提示8：解释分析是你的朋友

Hi all:

Let''s say I''m designing a database (Postgres 7.3) with a list of all
email accounts in a certain server:
CREATE TABLE emails (
clienteid INT4,
direccion VARCHAR(512) PRIMARY KEY,
login varchar(128) NOT NULL,
password VARCHAR(128),
dominio VARCHAR(256)
);
The PHBs want to have a log of when was an email account added, which
technician did it, when was it deleted, when did we have to reset its
password, etc.:
CREATE TABLE emails_log (
direccion varchar(512) references emails,
fecha date,
autor varchar(32),
texto varchar(1024)
);

"texto" would be a free form text field explaining what has been done.
Now, let''s suppose that an email account is deleted, and six months
later another user requests it and we add it again. Do we want to keep
an audit trail for the old "version" of that account? The PHBs say yes.
Which means that we can''t use the email address as primary key. Fine, we
add an "ID" column to the "emails" table and make it the primary key,
and point the foreign key in "emails_log" to that column. But now we
have two options, and here is my question:

-In "emails", the "direccion" column needs to be unique... but only for
the active email addresses (there can be 5, 10, or 20 dead addresses
called "lu***@domain2.com", but only one alive at the moment). We could
add an "active" boolean column to "emails", and write a custom
constraint to check this condition, but I find it ugly (and I saw
similar objections when another user came up with a similar problem some
time ago)...
-...Or we could create a table called "dead_emails", and add to it the
email addresses that we delete (using an ON DELETE trigger, perhaps).
Basically, store the deleted email accounts in another table... but then
we lose the referential integrity check in "emails_log".

The question is: what would you do? (I don''t really like the idea of
creating yet another "dead_emails_log" table pointing to "dead_emails";
I find it almost as ugly as the first one).

Paulo Jan.
DDnet.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

推荐答案

如果我这样做，我'' d制作一张桌子：

email_event_log：

email_address

活动

who_did_it

datestamp


然后你可以在事件发生时记录事件。事件：添加，删除，

PASSWORD等。使事件只有合法事件在事件中有效

列为了保持一致性，你很高兴。保持PHB的快乐！


轻松生成报告并查找给定电子邮件中发生的所有事件

地址等


Scott


2003年10月29日星期三09:38，Paulo Jan写道：

If I was doing this, I''d make a table:
email_event_log:
email_address
event
who_did_it
datestamp

Then you can make events be logged when the happen. Events: ADD, DELETE,
PASSWORD, etc. Make it so that only legal events are valid in the events
column for consistency and you are good to go. Keep the PHB''s happy!

Easy to generate reports and find all that has happened on a given email
address, etc.

Scott

On Wednesday 29 October 2003 09:38, Paulo Jan wrote:

大家好：

假设我正在设计一个数据库（Postgres 7.3），其中包含某个服务器中所有
电子邮件帐户的列表：

CREATE TABLE电子邮件（
clienteid INT4，
direccion VARCHAR（512）PRIMARY KEY，
login varchar（128）NOT NULL，
密码VARCHAR（128），
dominio VARCHAR（256）
）;

PHB想要记录添加电子邮件帐户的时间，技术人员做了什么，删除的时间，我们何时必须重置其
密码等：

CREATE TABLE emails_log（
direccion varchar（512）引用电子邮件，
日期，
autor varchar（32）， texto varchar（1024）
）;

" texto"将是一个自由格式文本字段，解释已经完成的工作。
现在，让我们假设一个电子邮件帐户被删除，六个月后，另一个用户请求它，我们再次添加它。我们是否希望保留旧版版本的审计线索。那个账户？ PHB说是。
这意味着我们不能使用电子邮件地址作为主键。好的，我们添加一个ID列到电子邮件 table并将其作为主键，然后将外键指向emails_log。到那个专栏。但是现在我们有两个选择，这是我的问题：

- 在电子邮件中，direccion和列必须是唯一的......但仅限于活动电子邮件地址（可能有5,10或20个死地址被称为lu *** @ domain2.com，但仅限于一个活着的人）。我们可以添加一个活跃的布尔列到电子邮件，并写一个自定义
约束来检查这种情况，但我发现它很难看（当我的另一个用户遇到类似的问题时，我看到了类似的异议很久以前）...
-...或者我们可以创建一个名为dead_emails的表，并添加我们删除的
电子邮件地址（可能使用ON DELETE触发器） 。
基本上，将已删除的电子邮件帐户存储在另一个表中...但是然后我们在emails_log中丢失了参照完整性检查。

问题是：什么会你做？ （我不太喜欢创建另一个指向dead_emails的dead_emails_log表格的想法;
我发现它几乎和第一个一样难看。）

Paulo Jan.
DDnet。

---------------------------（结束广播）---------------------------
提示8：解释分析是你的朋友

Hi all:

Let''s say I''m designing a database (Postgres 7.3) with a list of all
email accounts in a certain server:
CREATE TABLE emails (
clienteid INT4,
direccion VARCHAR(512) PRIMARY KEY,
login varchar(128) NOT NULL,
password VARCHAR(128),
dominio VARCHAR(256)
);
The PHBs want to have a log of when was an email account added, which
technician did it, when was it deleted, when did we have to reset its
password, etc.:
CREATE TABLE emails_log (
direccion varchar(512) references emails,
fecha date,
autor varchar(32),
texto varchar(1024)
);

"texto" would be a free form text field explaining what has been done.
Now, let''s suppose that an email account is deleted, and six months
later another user requests it and we add it again. Do we want to keep
an audit trail for the old "version" of that account? The PHBs say yes.
Which means that we can''t use the email address as primary key. Fine, we
add an "ID" column to the "emails" table and make it the primary key,
and point the foreign key in "emails_log" to that column. But now we
have two options, and here is my question:

-In "emails", the "direccion" column needs to be unique... but only for
the active email addresses (there can be 5, 10, or 20 dead addresses
called "lu***@domain2.com", but only one alive at the moment). We could
add an "active" boolean column to "emails", and write a custom
constraint to check this condition, but I find it ugly (and I saw
similar objections when another user came up with a similar problem some
time ago)...
-...Or we could create a table called "dead_emails", and add to it the
email addresses that we delete (using an ON DELETE trigger, perhaps).
Basically, store the deleted email accounts in another table... but then
we lose the referential integrity check in "emails_log".

The question is: what would you do? (I don''t really like the idea of
creating yet another "dead_emails_log" table pointing to "dead_emails";
I find it almost as ugly as the first one).

Paulo Jan.
DDnet.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------（播出结束）-------------- -------------

提示9：如果您的

加入专栏，计划员将无视您选择索引扫描的愿望数据类型不匹配

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match

在我与Andrew离线的进一步讨论中，我们提出了一个关于让PostgreSQL做的联合

的建议自动审核。这将是非常好的b $ b NICE，imho。任何输入？


Scott写道：

In my further discussion with Andrew offline, we came up with a joint
suggestion to have PostgreSQL do automatic auditing. This would be VERY
NICE, imho. Any input?

Scott wrote:

如果您可以在
表上翻转切换并自动拥有它，那将会很不错在另一个表中构建审计条目。


安德鲁回答：是的 - 这将是一个很棒的功能 - 自动审核...
也许你应该发布给某人（无论是谁？） PostgreSQL - 肯定会有重大的性能影响问题（可能是
而非桌面级别，字段/列级别会更好），但它对许多人来说都是一个福音...

It seems like it would be nice if you could flip a toggle on a
table and have it automatically build audit entries in another table.
Andrew replied: Yeah - that would be a great feature - automatic auditing...
Maybe you should post that to someone (whoever it would be?) at
PostgreSQL - sure, there would be major performance hit problems (maybe
rather than at table level, field/column level would be better), but it
would be a boon for many...

---------------------------（广播结束）------- --------------------

提示2：您可以使用取消注册命令一次性取消所有列表

（发送取消注册YourEmailAddressHere到 ma*******@postgresql.org ）

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

2003年10月29日星期三，Scott Chapman写道：

On Wed, 29 Oct 2003, Scott Chapman wrote:

在我与Andrew离线的进一步讨论中，我们出现了有一个联合建议让PostgreSQL做自动审核。这将非常好，非常好。任何输入？

Scott写道：

In my further discussion with Andrew offline, we came up with a joint
suggestion to have PostgreSQL do automatic auditing. This would be VERY
NICE, imho. Any input?

Scott wrote:

如果您可以在
表上翻转切换并让它自动构建审计条目，那就好了另一张桌子。

It seems like it would be nice if you could flip a toggle on a
table and have it automatically build audit entries in another table.

安德鲁回复：

Andrew replied:

是的 - 这将是一个很棒的功能 - 自动审核...
也许你应该发布到在PostgreSQL上有人（无论是谁？） - 肯定会有重大的性能问题（可能是
而不是表级别，字段/列级别会更好），但它
对许多人来说将是一个福音......

Yeah - that would be a great feature - automatic auditing...
Maybe you should post that to someone (whoever it would be?) at
PostgreSQL - sure, there would be major performance hit problems (maybe
rather than at table level, field/column level would be better), but it
would be a boon for many...

我喜欢这个主意。这样做会很好：


创建表格测试（名称文字，id序列主键）

带审核

（id keyid，10 cycle，fifo | stop）;


并有一个审核表，其中包含表格的历史视图，每个关键字最多10

深，或者要么它们要么是它们，所以那些早于
10的那些消失，或者当历史记录得到太深的时候，它会停止插入父母。


我猜测概念证明可以在plpgsql中完成，并且

审计部分被编程为触发前。


---------------------------（播出结束）-------------- -------------

提示8：解释分析是你的朋友

I like the idea. It would be kinda nice to do:

create table test (name text, id serial primary key)
with audit
(id keyid, 10 cycle,fifo|stop);

and have an auditing table with a historical view of the table up to 10
deep per key, and either have it either fifo them so the ones older than
10 disappear or have it stop inserts into the parent when the history gets
too deep.

I''d guess the proof of concept could be done in plpgsql, with the with
audit part programmed as a before trigger.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

这篇关于数据库设计问题：丑陋或参考完整性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！