[Mospft-devel] RE: [Mospft-admin] Meeting

[Powell, J. (EER) <Pow...@na...>]

> The only thing I didn't get about your schema was why did you
> include things like pointers in the schema, isn't that something
> which we worry about when building the system? Isn't the database
> schema just how the data is held?

In the following case:

> Transaction
>	ID		The key
>	Check		The check number (not used for savings)
>	To		The name to whom the check is made out
>	Reconciled	Whether the check has been reconciled (Boolean)
>	Withdrawal	Check/ATM Withdrawal/etc.
>	Deposit	Money going in.
>	Balance	Balance as of this transaction.
>	Next		ID of next transaction for this account.

I included the Next pointer, because it will actually be stored as part of
the database.  This way we can have 1 transaction table for standard bank
accounts, and any number of accounts pointing at that table.

However, I have been looking and for efficiency, it might be better to have
a different table for each of the accounts.  My original plan was to have a
single transaction table that all of the accounts used, but running through
the numbers, unless the user doesn't do a backup for 20 years, the saves
disk space for 5 accounts is only about 1 MB (this is 5 years writing 3
checks per day and making 2 deposits per week), hardly worth the wasted
computing time to do the linked list approach for the table.  So, I would
change to the following on my part:

User
	UserID	Name/Login
	Password	Some kind of security <--I like this comment
	Forename
	Surname
	DOB
	Account	Some how we need to figure out which accounts
			this user has access to so that we can display
			a list for him/her to choose from.  This could
			as simple as a CSV list, or as complex as a 
			linked list.

Bank Accounts
	Active	Is the account active (Boolean)
	Bank		This will be the banks 9 digit Routing Number, we
need
			to determine what the approach is in Europe, I think
			the 12 digit number is standard.  If it is, we can
use 
			this number as a lookup into a Bank Table.
	Account	This will contain the account number
	On-line	Whatever is needed to perform online banking with this
			bank.
	Balance	The calculated balance

Transaction
	Check		The check number (not used for savings)
	To		The name to whom the check is made out
	Reconciled	Whether the check has been Reconciled (Boolean)
	Withdrawal	Check/ATM Withdrawal/etc.
	Deposit	Money going in.
	Balance	Balance as of this transaction.