Question about database design

Hello,
Does anyone have any pointers about how to properly design a
"banking","accounting" database?
I would apprecieate any suggestions including URLs and books with more info
about the subject.
In particular, what is the "MOST PROPER WAY" to design a database that needs
to store and track the following.
1) There are members
2) Members pay a certain amount (each one is different) annually to continue
receiving the service
3) Members pay additional amount if they want to add features to their
service. Example: Order more of certain type of widgets.
4) A person can become a member only after the first payment (for the first
year plus registration fee) is received. Until this point the person who
registered is not a member yet.
What would be the proper database structure to store the members, their
payments, their status, and their "balances". Are there "balances" in this
case?
Thank you in advance!
ArsenThat is a huge subject. I particularly like the following
Louis Davison's book on Database design by Wrox (He is doing a rewrite for
Apress)
http://www.amazon.co.uk/exec/obidos/ASIN/1861004761/qid=1070564901/sr=1-1/ref=sr_1_2_1/202-9591868-9770231
The Data Modelling Handbook
http://www.amazon.co.uk/exec/obidos/ASIN/0471052906/202-9591868-9770231
Information Modelling and Relational Databases: From Conceptual Analysis to
Logical Design
http://www.amazon.co.uk/exec/obidos/ASIN/1558606726/202-9591868-9770231
Data Analysis for Database Design
http://www.amazon.co.uk/exec/obidos/ASIN/0750650869/202-9591868-9770231
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
news:%23H00%23ipuDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Does anyone have any pointers about how to properly design a
> "banking","accounting" database?
> I would apprecieate any suggestions including URLs and books with more
info
> about the subject.
> In particular, what is the "MOST PROPER WAY" to design a database that
needs
> to store and track the following.
> 1) There are members
> 2) Members pay a certain amount (each one is different) annually to
continue
> receiving the service
> 3) Members pay additional amount if they want to add features to their
> service. Example: Order more of certain type of widgets.
> 4) A person can become a member only after the first payment (for the
first
> year plus registration fee) is received. Until this point the person who
> registered is not a member yet.
> What would be the proper database structure to store the members, their
> payments, their status, and their "balances". Are there "balances" in this
> case?
> Thank you in advance!
> Arsen
>|||Hi Allan,
Thanks for your reply!
I understand the general concepts of "database design". I am looking
specifically at the proper way of designing "banking" or "accounting"
databases.
How would you design my example about members and payments?
Thanks,
Arsen
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uQ8A$opuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> That is a huge subject. I particularly like the following
> Louis Davison's book on Database design by Wrox (He is doing a rewrite for
> Apress)
>
http://www.amazon.co.uk/exec/obidos/ASIN/1861004761/qid=1070564901/sr=1-1/ref=sr_1_2_1/202-9591868-9770231
> The Data Modelling Handbook
> http://www.amazon.co.uk/exec/obidos/ASIN/0471052906/202-9591868-9770231
> Information Modelling and Relational Databases: From Conceptual Analysis
to
> Logical Design
> http://www.amazon.co.uk/exec/obidos/ASIN/1558606726/202-9591868-9770231
> Data Analysis for Database Design
> http://www.amazon.co.uk/exec/obidos/ASIN/0750650869/202-9591868-9770231
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
> news:%23H00%23ipuDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > Does anyone have any pointers about how to properly design a
> > "banking","accounting" database?
> >
> > I would apprecieate any suggestions including URLs and books with more
> info
> > about the subject.
> >
> > In particular, what is the "MOST PROPER WAY" to design a database that
> needs
> > to store and track the following.
> > 1) There are members
> > 2) Members pay a certain amount (each one is different) annually to
> continue
> > receiving the service
> > 3) Members pay additional amount if they want to add features to their
> > service. Example: Order more of certain type of widgets.
> > 4) A person can become a member only after the first payment (for the
> first
> > year plus registration fee) is received. Until this point the person who
> > registered is not a member yet.
> >
> > What would be the proper database structure to store the members, their
> > payments, their status, and their "balances". Are there "balances" in
this
> > case?
> >
> > Thank you in advance!
> >
> > Arsen
> >
> >
>|||Louis using a checking system example in his book.
I personally find ORM to do the conceptual modelling very very good.
You look to have
User Supertype (Registered and Members as subtypes) they will both share
common properties except Member will have stuff like a JoinDate and the
RegisteredUser will not. Both will maybe have an enquiry date
They all make payments.
Balance can either be stored with the Member or calculated as needed (I
prefer calculated)
This is really something you can model based on the information you have
collected which is why I love ORM.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
news:OPFxKvpuDHA.2492@.TK2MSFTNGP12.phx.gbl...
> Hi Allan,
> Thanks for your reply!
> I understand the general concepts of "database design". I am looking
> specifically at the proper way of designing "banking" or "accounting"
> databases.
> How would you design my example about members and payments?
> Thanks,
> Arsen
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uQ8A$opuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > That is a huge subject. I particularly like the following
> >
> > Louis Davison's book on Database design by Wrox (He is doing a rewrite
for
> > Apress)
> >
>
http://www.amazon.co.uk/exec/obidos/ASIN/1861004761/qid=1070564901/sr=1-1/ref=sr_1_2_1/202-9591868-9770231
> >
> > The Data Modelling Handbook
> > http://www.amazon.co.uk/exec/obidos/ASIN/0471052906/202-9591868-9770231
> >
> > Information Modelling and Relational Databases: From Conceptual Analysis
> to
> > Logical Design
> > http://www.amazon.co.uk/exec/obidos/ASIN/1558606726/202-9591868-9770231
> >
> > Data Analysis for Database Design
> > http://www.amazon.co.uk/exec/obidos/ASIN/0750650869/202-9591868-9770231
> >
> >
> >
> > --
> > --
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
> > news:%23H00%23ipuDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > Does anyone have any pointers about how to properly design a
> > > "banking","accounting" database?
> > >
> > > I would apprecieate any suggestions including URLs and books with more
> > info
> > > about the subject.
> > >
> > > In particular, what is the "MOST PROPER WAY" to design a database that
> > needs
> > > to store and track the following.
> > > 1) There are members
> > > 2) Members pay a certain amount (each one is different) annually to
> > continue
> > > receiving the service
> > > 3) Members pay additional amount if they want to add features to their
> > > service. Example: Order more of certain type of widgets.
> > > 4) A person can become a member only after the first payment (for the
> > first
> > > year plus registration fee) is received. Until this point the person
who
> > > registered is not a member yet.
> > >
> > > What would be the proper database structure to store the members,
their
> > > payments, their status, and their "balances". Are there "balances" in
> this
> > > case?
> > >
> > > Thank you in advance!
> > >
> > > Arsen
> > >
> > >
> >
> >
>|||Hi Allan,
I think you pretty much pin-pointed my question!
"Balance can either be stored with the Member or calculated as needed (I
preferer calculated)"
This is exactly what I was referring to. Which way is better and why?
You say "calculated"... How would it be calculated? Summing the records in
the Payments table? Are there downsides to doing this calculation?
Why do you preferer the "calculated" method? What is the "proper" method and
why? I would like to find information exactly about this type of design...
accounting/banking dealing with balances.
Thanks!
Arsen
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eIm8ASquDHA.3532@.TK2MSFTNGP11.phx.gbl...
> Louis using a checking system example in his book.
> I personally find ORM to do the conceptual modelling very very good.
> You look to have
> User Supertype (Registered and Members as subtypes) they will both share
> common properties except Member will have stuff like a JoinDate and the
> RegisteredUser will not. Both will maybe have an enquiry date
> They all make payments.
> Balance can either be stored with the Member or calculated as needed (I
> prefer calculated)
> This is really something you can model based on the information you have
> collected which is why I love ORM.
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
> news:OPFxKvpuDHA.2492@.TK2MSFTNGP12.phx.gbl...
> > Hi Allan,
> >
> > Thanks for your reply!
> >
> > I understand the general concepts of "database design". I am looking
> > specifically at the proper way of designing "banking" or "accounting"
> > databases.
> >
> > How would you design my example about members and payments?
> >
> > Thanks,
> > Arsen
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:uQ8A$opuDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > That is a huge subject. I particularly like the following
> > >
> > > Louis Davison's book on Database design by Wrox (He is doing a rewrite
> for
> > > Apress)
> > >
> >
>
http://www.amazon.co.uk/exec/obidos/ASIN/1861004761/qid=1070564901/sr=1-1/ref=sr_1_2_1/202-9591868-9770231
> > >
> > > The Data Modelling Handbook
> > >
http://www.amazon.co.uk/exec/obidos/ASIN/0471052906/202-9591868-9770231
> > >
> > > Information Modelling and Relational Databases: From Conceptual
Analysis
> > to
> > > Logical Design
> > >
http://www.amazon.co.uk/exec/obidos/ASIN/1558606726/202-9591868-9770231
> > >
> > > Data Analysis for Database Design
> > >
http://www.amazon.co.uk/exec/obidos/ASIN/0750650869/202-9591868-9770231
> > >
> > >
> > >
> > > --
> > > --
> > >
> > > Allan Mitchell (Microsoft SQL Server MVP)
> > > MCSE,MCDBA
> > > www.SQLDTS.com
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > > "Arsen Vladimirskiy" <arsen@.emergency24.com> wrote in message
> > > news:%23H00%23ipuDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > Does anyone have any pointers about how to properly design a
> > > > "banking","accounting" database?
> > > >
> > > > I would apprecieate any suggestions including URLs and books with
more
> > > info
> > > > about the subject.
> > > >
> > > > In particular, what is the "MOST PROPER WAY" to design a database
that
> > > needs
> > > > to store and track the following.
> > > > 1) There are members
> > > > 2) Members pay a certain amount (each one is different) annually to
> > > continue
> > > > receiving the service
> > > > 3) Members pay additional amount if they want to add features to
their
> > > > service. Example: Order more of certain type of widgets.
> > > > 4) A person can become a member only after the first payment (for
the
> > > first
> > > > year plus registration fee) is received. Until this point the person
> who
> > > > registered is not a member yet.
> > > >
> > > > What would be the proper database structure to store the members,
> their
> > > > payments, their status, and their "balances". Are there "balances"
in
> > this
> > > > case?
> > > >
> > > > Thank you in advance!
> > > >
> > > > Arsen
> > > >
> > > >
> > >
> > >
> >
> >
>