最佳实践 [英] Best practice
问题描述
我正在为多个客户建立一个基于网络的拍卖系统。我有
拍卖，物品和发票，我需要为其分配生成的密钥。由于
这些实体中的每一个都由一个表格表示，因此密钥被指定为GENERATED BY DEFAULT
。虽然这很有效，并且很容易以编程方式参考
，但我想知道它是否是最好的技术。所有的按键
都来自一个序列，不依赖于客户或
特定拍卖。每个客户都有一个auction_id序列为
，每个拍卖的item_id序列会更好吗（客户永远不会看到这些列中的任何一个的
，但是没有这些
表的自然键是在实例化时准备好并且已知的）和每个客户或拍卖的
invoice_no序列？如果是这样，我应该如何实现这些以最大限度地减少SQL调用并确保不重复？
对于序列，我可以执行以下操作：
INSERT INTO拍卖会（client_id，auction_id，...）
VALUES（$ client，（SELECT NEXTVAL
FOR auction_is_seq_ $ client），... ）
如果我使用MAX（auction_id），在一次原子操作中选择
MAX并插入新行的语法是什么？ br />
如果我为next_auction_id使用与每个用户相关联的列，那么
就会出现同样的问题。
相同问题，只有在我用一个INSERT语句一次添加许多
行时才会增加难度。
客户端，（选择NEXTVAL
FOR auction_is_seq _
客户端），...）
如果我使用MAX（auction_id），选择
MAX和I的语法是什么NSERT在一个原子操作中的新行？
如果我使用与每个用户相关联的列next_auction_id，那么
也会出现同样的问题。
同样的问题，只有在我用一个INSERT语句一次添加许多
行时才会增加难度。
" Bob Stearns" < rs ********** @ charter.netwrote in message
news：Et ***************** @ fe06。 lga ...
我正在为多个客户建立一个基于网络的拍卖系统。我有
拍卖，物品和发票，我需要为其分配生成的密钥。由于
这些实体中的每一个都由一个表表示，因此键被指定为
GENERATED BY DEFAULT。虽然这很有效，并且很容易以编程方式参考
，但我想知道它是否是最好的技术。所有的按键
都是从一个序列中提取的，而不是取决于客户或特定的b
拍卖。为每个
客户设置一个auction_id序列，每次拍卖的item_id序列会更好吗（客户从来没有看到这些列中的一个是
，但是没有这些表的自然键是
准备好并在实例化时知道）和invoice_no序列为每个客户或拍卖的
？如果是这样，我应该如何实现这些？
最小化SQL调用并确保没有重复？通过序列，我可以做
之类的事情：
INSERT INTO拍卖（client_id，auction_id，...）
VALUES （
I''m building a web based auction system for multiple clients. I have
auctions, items, and invoices I need to assign generated keys for. Since
each of these entities is represented by a table, the keys are assigned
as GENERATED BY DEFAULT. While this works, and is easy to reference
programmatically, I wonder if it is the best technique. All of the keys
are drawn from one sequence, not depending on the customer or the
specific auction. Would it be better to have an auction_id sequence for
each client, an item_id sequence for each auction (the client never sees
either one of these columns, but there is no natural key for these
tables that is ready and known when they are instantiated) and
invoice_no sequence for each client or auction? If so, how should I
implement these to both minimize SQL calls and ensure no duplication?
With sequences, I can do something like:
INSERT INTO auctions (client_id, auction_id, ...)
VALUES ($client, (SELECT NEXTVAL
FOR auction_is_seq_$client), ...)
If instead I use the MAX(auction_id), what is the syntax to select the
MAX and INSERT the new row in one atomic operation?
If I use a column associated with each user for next_auction_id, the
same question occurs.
The same question, only increased in difficulty occurs when I have many
rows to add at once with one INSERT statement.解决方案client, (SELECT NEXTVAL
FOR auction_is_seq_
client), ...)
If instead I use the MAX(auction_id), what is the syntax to select the
MAX and INSERT the new row in one atomic operation?
If I use a column associated with each user for next_auction_id, the
same question occurs.
The same question, only increased in difficulty occurs when I have many
rows to add at once with one INSERT statement.
"Bob Stearns" <rs**********@charter.netwrote in message
news:Et*****************@fe06.lga...I''m building a web based auction system for multiple clients. I have
auctions, items, and invoices I need to assign generated keys for. Since
each of these entities is represented by a table, the keys are assigned as
GENERATED BY DEFAULT. While this works, and is easy to reference
programmatically, I wonder if it is the best technique. All of the keys
are drawn from one sequence, not depending on the customer or the specific
auction. Would it be better to have an auction_id sequence for each
client, an item_id sequence for each auction (the client never sees either
one of these columns, but there is no natural key for these tables that is
ready and known when they are instantiated) and invoice_no sequence for
each client or auction? If so, how should I implement these to both
minimize SQL calls and ensure no duplication? With sequences, I can do
something like:
INSERT INTO auctions (client_id, auction_id, ...)
VALUES (
这篇关于最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
