430 reputation
49
bio website None
location California, USA
age 54
visits member for 3 years, 4 months
seen Jul 28 at 16:35
Long-time Informix user and developer, experienced in C and Unix (many variants). Email: jonathan.leffler@gmail.com

Jul
28
revised How to join the latest previous record with SQL
Use correct () instead of {} in CREATE TABLE statement; indent body of statement
Jul
28
suggested approved edit on How to join the latest previous record with SQL
Sep
9
comment Better Indexing for MySQL?
As with all the best questions — it depends. You need unique constraints (indexes) on the combinations of columns for your candidate keys (one of which is your primary key; it may be the sole candidate key). You don't need a separate single column index on the leading column of the unique constraint indexes. Whether you need indexes on other columns depends on the queries you're going to be running. If they can be answered well with the candidate key indexes, the extras are just getting in the way. If there are frequently run queries which will benefit, then add extra indexes. Measure!
Aug
20
awarded  Yearling
May
20
awarded  Constituent
May
14
answered Can't Select a View on Informix
May
13
awarded  Caucus
Apr
11
comment SQL Index order and performance based on cardinality and data
It looks like a slam dunk for an index on B(Id1, Id2) or B(Id1, Id2, Type_Code) to improve performance. At least this index would support the join condition more simply, leaving the type_code check to the end, instead of forcing it at the beginning. The COALESCE may prevent the optimizer using the index and thus speed things up, paradoxically.
Feb
12
comment SQL as a Storage Definition Language (SDL)
The SDL stuff is almost anything that's not specified by the SQL standard that is different in every DBMS and that specifies anything to do with how or where the data in the relevant table is stored. So 'yes: SET storage_engine=MYISAM is a MySQL-specific example of SDL'.
Feb
8
answered Transaction in SQL Server
Feb
8
comment Transaction in SQL Server
Which DBMS are you using? (Given the [dbo].[SP3] notation, it's likely to be MS SQL Server, but it's as well to be specific.) Are autonomous transactions a possibility?
Dec
11
comment Why can't I create a database in a 107GB dbspace I created for my instance?
Not good. Did you run out of disk space? If onstat -d does not show it, then you can remove the file and do the onspaces command suggested by rok observing its output and exit status (and double-checking with onstat -d). Either way, there is little doubt that it should have worked via the wizard, and there might be cause for reporting a bug to IBM.
Dec
11
answered Why can't I create a database in a 107GB dbspace I created for my instance?
Oct
31
answered Two-phase commit for distributed transactions: what if a commit failed?
Oct
31
comment How should I best design the tables and relationships, given the following rules?
I'm logged in, but it is insisting that I login...I think it has got Sandy in its brains and is confused about whether Oregon has hurricanes or not. Or something.
Oct
31
comment How should I best design the tables and relationships, given the following rules?
You've omitted an entity from your descriptions, then. There is a journal table of some sort needed, which identifies actions taken on objects in the contracts and/or receipts table (and other salient information, such as employee, customer, contract, amount of money, etc). You say you're adding an entry, but it isn't a new receipt, and it isn't a new contract (and it definitely isn't a contract item), so it must be something else — a journal entry.
Oct
31
comment How should I best design the tables and relationships, given the following rules?
Then three sequences and a composite key are the way to go. Just be careful to ensure that your hapless programmer(s) never make a mistake. You could still use an internal serial column as the contract number to avoid composite joins; your Contracts table would have a ContractSerialNo (unique, probably primary key), and a composite (ContractType, SequenceNo) key. Your contract items table would x-ref the ContractSerialNo; so would the Receipts table, most likely. The three separate sequences correspond to three separate pads.
Oct
31
comment How should I best design the tables and relationships, given the following rules?
And I understood all that, I thought, and I don't see where the problem is. What am I missing? An interest payment creates a new receipt — with a new receipt number — by inserting a record into the receipts table. A 'pulled' pawn transaction is marked by updating the contract to indicate that it is no longer active (without creating a receipt). A redeemed pawn transaction is marked by updating the contract to indicate that it is no longer active (without creating a receipt). A matured pawn contract (unredeemed?) is marked by updating the contract (without creating a receipt).
Oct
31
comment How should I best design the tables and relationships, given the following rules?
Why would there be three separate counters? That's ... less than entirely sensible. They're all contracts; they don't need to be sequential within a particular type. Oh well, the customer (pawn shop owner, I suppose) is always right. Then you definitely use sequences. One sequence for Buy Contracts; one sequence for Sell Contracts; one sequence for Pawn Contracts. Your primary key on Contracts becomes a composite of type (P, S, B) and the sequence value from the relevant sequence; your foreign keys become composite keys. It makes life unnecessarily complex, but it can be done if you insist.
Oct
31
comment How should I best design the tables and relationships, given the following rules?
What's the problem? There'll be a new record in the Receipts table with a new serial number when you create (insert) an interest payment receipt, or when you create a new contract of any sort. And that would be automatically generated. You'd only use the SELECT MAX technique if you refuse to use either a SERIAL (which simply doesn't need it) or a SEQUENCE (where the <sequence-name>.NEXTVAL (sp?) function returns the next available sequence number).