I was not aware of composite keys so I quickly learned what they where and I
have decided to contemplate my database design. I searched for performance
issues with using composite keys and I was not able to find much information
related to SQLite.
Igor Tandetnik wrote:
>
> Cnichols <[EMAIL PROTECTED]> wrote:
>> REPLACE INTO (AccountId, MemberId, SessionId, State)
>> SELECT (M.Id || '-' || S.Id), M.Id, S.Id, 4
>> From Sessions AS S, Members AS M
>> LEFT JOIN AccountState AS AST ON S.Id = AST.SessionId
>> WHERE S.OverdueHandled = 0
>> AND S.Grace < DATE('NOW')
>> --AND AST.State != 1
>
> Right off the bat, I don't see Members table being joined to anything.
> Do you really mean to use a full cartesian product?
>
>> Based on the nature of how REPLACE INTO works I created the AccountID
>> field which is just unique text so a replace can occur.
>
> You don't need an artificial single field for that. You can declare a
> composite key or index, e.g.
>
> create table AccountState (
> MemberId, SessionId, State,
> PRIMARY KEY (MemberId, SessionId)
> )
>
>> To save on diskspace I only keep the alert only when the session has
>> been paid or becomes dilequent so for the other X days there is no
>> need to keep neutral status in the alert table.
>> So a record of an account will not be there unless paid or dilequent
>> (which includes due, grace, overdue, cumalative) so I need to insert
>> a record if its not there but I don't want to replace and paid
>> accounts, but i can overwrite all the other dilquent with a high
>> predecesor
>
> This narrative assumes that the reader knows a) banking terminology; b)
> the exact way it is represented in your data model. You are much likely
> to get help if you restate all these conditions in terms of your tables
> and fields.
>
> Igor Tandetnik
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Help-Once-Again-tf2745497.html#a7666128
Sent from the SQLite mailing list archive at Nabble.com.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
