Dagfinn Ilmari Mannsåker writes:
Smylers smyl...@stripey.com writes:
Here it is in Postgres's own function language, which goes by the
awkwardly written name PL/pgSQL:
CREATE OR REPLACE FUNCTION set_document_version_num() RETURNS TRIGGER AS
$FN$
BEGIN
NEW.version :=
Auto-increment a double column primary key, something like:
uid int(11) not null auto_increment, revision int(11) not null
auto_increment, primary key(uid, revision)
; works in mysql. You won't have your concurrent query issues there, for
the most part anyway. Maybe marking a records as
On 18/11/13 11:18, Philip Skinner wrote:
Auto-increment a double column primary key, something like:
uid int(11) not null auto_increment, revision int(11) not null
auto_increment, primary key(uid, revision)
; works in mysql. You won't have your concurrent query issues there,
for the most
On 8 Nov 2013, at 19:33, David Cantrell da...@cantrell.org.uk wrote:
[...]
Because you might need to know which of two events at 2013-11-08Z19:31:04
happened first. Sure you could use microseconds or whatever to get better
resolution, but all that does is make the problem less likely, it
On 10/11/2013, at 4:24, Peter Corlett ab...@cabal.org.uk wrote:
On 8 Nov 2013, at 19:33, David Cantrell da...@cantrell.org.uk wrote:
[...]
Because you might need to know which of two events at 2013-11-08Z19:31:04
happened first. Sure you could use microseconds or whatever to get better
Smylers smyl...@stripey.com wrote:
William Blunn writes:
Instead of storing a version ID (e.g. 1, 2, 3, 4, 5, 6), you could
store a SERIAL.
So for one document ID, you might store versions 10, 11, 12, 50, 75,
87.
This was my immediate thought as well, but I'd probably cheat and declarr
On 11/08/2013 08:17 AM, James Laver wrote:
Smylers smyl...@stripey.com wrote:
William Blunn writes:
Instead of storing a version ID (e.g. 1, 2, 3, 4, 5, 6), you could
store a SERIAL.
So for one document ID, you might store versions 10, 11, 12, 50, 75,
87.
This was my immediate thought as
Mark Stringer m...@repixl.com wrote:
From the OP: But version should start at 1 for each document and be
consecutive.
I'll grant that I've assumed that was to be more friendly to the end user, but
in that case, it really is just cunning use of the .. operator.
I'd guess that the
On 08/11/2013, at 19:38, Mark Stringer m...@repixl.com wrote:
On 11/08/2013 08:17 AM, James Laver wrote:
Smylers smyl...@stripey.com wrote:
William Blunn writes:
Instead of storing a version ID (e.g. 1, 2, 3, 4, 5, 6), you could
store a SERIAL.
So for one document ID, you might store
James Laver writes:
Smylers smyl...@stripey.com wrote:
William Blunn writes:
Instead of storing a version ID (e.g. 1, 2, 3, 4, 5, 6), you could
store a SERIAL.
So for one document ID, you might store versions 10, 11, 12, 50,
75, 87.
This was my immediate thought as well,
On Fri, Nov 8, 2013 at 10:06 AM, Smylers smyl...@stripey.com wrote:
James Laver writes:
This was my immediate thought as well, but I'd probably cheat and
declarr the document version numbers to be oureky decorative
^^
“purely”, I presume?
I wrote:
Hello. I'm designing a database schema, and am interested in any
wisdom folk can share over a few aspects of it:
Thanks for all the advice so far. One more question I forgot to ask in
the initial mail:
A discount can either be a percentage or a value in euros. I can think
of several
I must say I quite like playing with triggers. A well encapsulated trigger
shouldn't take too much developer time, and so long as people read the schema
definitions, and the trigger is correctly documented/linked to in the right
places (which may be tricky to anticipate in advance, where the
On Fri, Nov 8, 2013 at 11:43 AM, Smylers smyl...@stripey.com wrote:
• Separate fields for discount_percentage and discount_amount. This has
the disadvantage of needing to ensure that a record doesn't have both
fields set.
If the business concocts another discount type in future, this
On 8. 11. 2013 10:06, Smylers wrote:
James Laver writes:
Smylers smyl...@stripey.com wrote:
William Blunn writes:
Instead of storing a version ID (e.g. 1, 2, 3, 4, 5, 6), you could
store a SERIAL.
So for one document ID, you might store versions 10, 11, 12, 50,
75, 87.
This was my
On 08/11/13 11:43, Smylers wrote:
• Have separate euro_discount and percentage_discount tables, then when
a discount applies create a record in the appropriate table. This
avoids any NULLs in the DB (something I've seen advocated as good
database design), but it still requires ensuring
Abigail writes:
On Thu, Nov 07, 2013 at 01:03:00PM +, Smylers wrote:
version should start at 1 for each document and be
I've used triggers to enforce business rules like this in the past
Mark Stringer writes:
I've used BEFORE INSERT triggers to select the max existing version
Dirk Koopman writes:
It all depends on the application and your view as to where the
business might go in the future.
Database design should only be performed by a qualified soothsayer?
James Laver writes:
On Fri, Nov 8, 2013 at 11:43 AM, Smylers smyl...@stripey.com wrote:
• Separate
On Fri, Nov 08, 2013 at 11:43:08AM +, Smylers wrote:
I wrote:
Hello. I'm designing a database schema, and am interested in any
wisdom folk can share over a few aspects of it:
Thanks for all the advice so far. One more question I forgot to ask in
the initial mail:
A discount can
Abigail writes:
On Fri, Nov 08, 2013 at 11:43:08AM +, Smylers wrote:
A discount can either be a percentage or a value in euros. I can
think of several suboptimal ways of representing this:
I opted for two columns, additional and percentage, the first
defaulting to 0, the other to
On Fri, Nov 8, 2013 at 3:02 PM, Abigail abig...@abigail.be wrote:
I opted for two columns, additional and percentage, the first
defaulting to 0, the other to 1. Prices can then be easily calculated
as original_price * percentage + additional. No conditionals needed.
And the calculation is
On Fri, Nov 8, 2013 at 12:38 AM, Mark Stringer m...@repixl.com wrote:
I'd guess that the date_created is being stored in the table, so could be
used to order the records without the need for a serial, if ordering is the
only concern.
Never use date/times as unique identifiers/sort keys.
On Fri, Nov 8, 2013 at 5:22 PM, Yitzchak Scott-Thoennes
sthoe...@gmail.com wrote:
On Fri, Nov 8, 2013 at 12:38 AM, Mark Stringer m...@repixl.com wrote:
I'd guess that the date_created is being stored in the table, so could be
used to order the records without the need for a serial, if ordering
On 08/11/2013 18:08, James Laver wrote:
On Fri, Nov 8, 2013 at 5:22 PM, Yitzchak Scott-Thoennes
sthoe...@gmail.com wrote:
On Fri, Nov 8, 2013 at 12:38 AM, Mark Stringer m...@repixl.com wrote:
I'd guess that the date_created is being stored in the table, so could be
used to order the records
Hello. I'm designing a database schema, and am interested in any wisdom
folk can share over a few aspects of it:
• A document can have versions. There's a document table and a
document_version_contents table, where each document_version_contents
record references document.id and has a version
On Thu, Nov 07, 2013 at 01:03:00PM +, Smylers wrote:
Hello. I'm designing a database schema, and am interested in any wisdom
folk can share over a few aspects of it:
• A document can have versions. There's a document table and a
document_version_contents table, where each
On 11/07/2013 01:28 PM, Abigail wrote:
I'm not familiar with Postgres, but I've used triggers to enforce
business rules like this in the past (using Sybase). In Sybase,
triggers are run before a transaction is committed, and you can
rollback a transaction frow within a trigger.
On an insert,
On Postgres and triggers. You can have them to run at any time you want:
http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
On 07/11/2013 13:03, Smylers wrote:
That is, if there is a record for document_id = 3846, version = 6
then there should also be records for version 1 to 5 of that document
ID. Is there a way of enforcing that at the DB level? We're using
Postgres, if that makes a difference.
Instead
On 07/11/2013 13:03, Smylers wrote:
A purchase consists of ordering one product from a supplier. Each
product is only available from a single supplier. So a record in the
purchase table just needs to store a product ID, and by linking
through the product table that defines the supplier
William Blunn writes:
On 07/11/2013 13:03, Smylers wrote:
A purchase consists of ordering one product from a supplier. Each
product is only available from a single supplier. So a record in
the purchase table just needs to store a product ID, and by
linking through the product
William Blunn writes:
On 07/11/2013 13:03, Smylers wrote:
That is, if there is a record for document_id = 3846, version = 6
then there should also be records for version 1 to 5 of that
document ID. Is there a way of enforcing that at the DB level?
We're using Postgres, if that
32 matches
Mail list logo
