Re: Counting money

["Alexey Parshin" <alexeyp@gmail.com>]

Well, this designed to work well for three entities. If we expect more - we need different structure. 

In one version of that structure, we should have the owner object id and owner object type (or account type), and owner object id can't be reference :( Such structure can be reasonably well controlled with the stored procs, but the selects to show all the accounts can be complex and costly.

In another version, we may have a set of proxy tables, one per entity, every record referring to account and entity. Then we can use unions if we need to show several different types of accounts in one select. I like this more, actually, since we can have at least some support from the database.

2007/6/9, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com>:

So, what happens, when we discover another entity that might need an
account?

Alexey Parshin wrote:
> I forgot to mention: every payment should have a comment, and, maybe,
> some reference to operation. In the last case we would need a table of
> operations. Every operation may group transactions together.
>
> 2007/6/8, Alexey Parshin <alexeyp@gmail.com <mailto:alexeyp@gmail.com >>:
>
>     Ok, from what is said, I can make the following conclusion:
>
>     Any money transaction in UU may belong to one of the accounts :
>     a) person
>     b) class
>     c) course
>
>     Therefore, the most suitable structure for the account record is:
>
>     CREATE TABLE account_list (
>       al_id serial primary key ,
>       al_person int ,
>       al_class int ,
>       al_course int
>     );
>
>     I don't show foreign key here, but they should be defined for all
>     the references {person,class,course}. Only one of the fields
>     al_person, al_class, al_course can be NOT NULL in any given record.
>     If we agree on this structure, I'd add an automatic creation of
>     account upon creation of any of: { person_list, course_class,
>     study_course } records.
>
>     We may need some extra fields for the account records - I'm opened
>     for suggestions. The next step is payment_history table. The draft
>     for the structure is:
>
>     CREATE TABLE payment_history (
>       ph_id serial primary key ,
>       ph_debit_acount int ,
>       ph_credit_account int ,
>       pp_date timestamp not null ,
>       pp_amount decimal(16,2)
>     );
>
>     Foreign keys are not shown but obvious.
>
>     2007/6/8, Alexey Parshin <alexeyp@gmail.com
>     <mailto: alexeyp@gmail.com>>:
>
>         2007/6/8, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com
>         <mailto: ilya@total-knowledge.com>>:
>
>             Alexey Parshin wrote:
>             > We currently have 1-1 relations between course and class.
>             WHAT???
>
>
>         Sorry, my bad, 1-N. There is still only one course for any class..
>
>
>             > So, the money can be distributed directly from class. I'm
>             asking,
>             > because if we have more than two source/destination types
>             (more than
>             > person and class), then it requires different structure
>             for the
>             > tables. (I'm still thinking on the best implementation).
>             >
>             > 2007/6/8, Ilya A. Volynets-Evenbakh <
>             ilya@total-knowledge.com <mailto:ilya@total-knowledge.com >
>             > <mailto: ilya@total-knowledge.com
>             <mailto:ilya@total-knowledge.com>>>:
>             >
>             >     Course accounts are needed, since course authors will
>             be drawing money
>             >     from them.
>             >     There is also class->course transaction possibility.
>             >
>             >     Alexey Parshin wrote:
>             >     > First of all, I needed to confirm the 'account' entity.
>             >     >
>             >     > Now, as far as I see, these transactions always
>             include to
>             >     parties: a
>             >     > person account and a class account.
>             >     > A person may be one of: author, teacher, student,
>             admin, translator,
>             >     > or anonymous (for donations).
>             >     > I'm not sure about course, however. I need to
>             understand, if we need
>             >     > other source/destination accounts (besides person
>             and class) and
>             >     what for.
>             >     >
>             >     > As soon as we have account system, the stored procs
>             handling the
>             >     money
>             >     > to/from these accounts can use transaction rules.
>             >     >
>             >     > 2007/6/7, Ilya A. Volynets-Evenbakh <
>             ilya@total-knowledge.com <mailto:ilya@total-knowledge.com>
>             >     <mailto: ilya@total-knowledge.com
>             <mailto:ilya@total-knowledge.com>>
>             >     > <mailto: ilya@total-knowledge.com
>             <mailto:ilya@total-knowledge.com>
>             >     <mailto: ilya@total-knowledge.com
>             <mailto:ilya@total-knowledge.com>>>>:
>             >     >
>             >     >     As far as sources/destinations go, I could also
>             think
>             >     >     of translators as destination (not sure we need
>             it within
>             >     >     the system, but probably not much extra cost)
>             and also
>             >     >     there is one other source - generic donation
>             (could be even
>             >     >     from outside person that doesn't sign up to
>             anything at all).
>             >     >
>             >     >     Oh, and people might want to donate to Total
>             Knowledge,
>             >     >     for writing such a wonderful system ;-) Also
>             not sure if we
>             >     >     need to integrate that into UU itself, or just
>             link to some
>             >     >     separate page for that.
>             >     >
>             >     >     Now, on top of that all, we need to find a way
>             to define
>             >     >     distribution rules/procedures for shared
>             accounts (class,
>             >     >     course)
>             >     >
>             >     >     Alexey Parshin wrote:
>             >     >     > Gentlemen,
>             >     >     >
>             >     >     > Since we gonna have a money traffic (well, at
>             least for
>             >     closed
>             >     >     > server), we need to define - what are the
>             possible sources and
>             >     >     > destinations of these money.
>             >     >     >
>             >     >     > I can think of few sources:
>             >     >     >
>             >     >     > 1) Students are paying for class:
>             >     >     >   source: a student, destination: a class account
>             >     >     >
>             >     >     > 2) Teachers are paid for teaching:
>             >     >     >   source: a class account, destination: a
>             teacher
>             >     >     >
>             >     >     > 3) Authors are paid for the authorship:
>             >     >     >   source: a class account, destination: an author
>             >     >     >
>             >     >     > How does that sounds for you? This creates an
>             entity
>             >     'account'..
>             >     >     >
>             >     >     > --
>             >     >     > Alexey Parshin,
>             >     >     > http://www.sptk.net
>             >     >
>             >     >     --
>             >     >     Ilya A. Volynets-Evenbakh
>             >     >     Total Knowledge. CTO
>             >     >     http://www.total-knowledge.com
>             >     >
>             >     >
>             >     >
>             >     >
>             >     > --
>             >     > Alexey Parshin,
>             >     > http://www.sptk.net
>             >
>             >     --
>             >     Ilya A. Volynets-Evenbakh
>             >     Total Knowledge. CTO
>             >     http://www.total-knowledge.com
>             >
>             >
>             >
>             >
>             > --
>             > Alexey Parshin,
>             > http://www.sptk.net
>
>             --
>             Ilya A. Volynets-Evenbakh
>             Total Knowledge. CTO
>             http://www.total-knowledge.com
>             <http://www.total-knowledge.com>
>
>
>
>
>         --
>
>         Alexey Parshin,
>         http://www.sptk.net
>
>
>
>
>     --
>
>     Alexey Parshin,
>     http://www.sptk.net
>
>
>
>
> --
> Alexey Parshin,
> http://www.sptk.net

--
Ilya A. Volynets-Evenbakh
Total Knowledge. CTO
http://www.total-knowledge.com

-- 
Alexey Parshin,
http://www.sptk.net