Le 05/01/2014 21:25, Doctor John a écrit :
> I am, as I have said several times to Stephen, not a database person.
> However, I seem to have got lumbered with a (non-paying) job; to whit,
> the design of a database to document 13th and 14th century bank
> transactions using their surviving ledgers.
> 
> Originally, I thought a flat file would be sufficient but the more I
> think about it the more I'm convinced I was wrong. I attach a very small
> png that shows my current thinking.
> 
> To explain: each banking family (Gallerani, Borromei etc) has several
> branches (London, Paris, Bruges ...). Each branch has several ledgers
> (numbered by series) and every ledger contains several hundred items
> containing details of transactions. Transaction1 is the initial approach
> by the Actor to the branch and Transaction2 is the future outcome
> (either payment to the same or a different actor and at the same or a
> different bank/branch).
> 
> Ignoring the complexities of writing the code to view, manipulate and
> play with the database, does this look sane to you? (BTW Solid lines
> indicate Identifying Relationships and broken lines in Non-Identifying
> Relationships)
> 
> John
> 

I stand by the objection that tables named from numbered "object" have
the wrong name. (old rule: if you have to number the tables's name, you
are doing it wrong)
Transaction1 might be BlackInk, Transaction2 RedInk, if you use the ink
colour of that time.

It seems that each item has at most one transaction#1 and at most one
transaction#2.

Can an item have no transaction at all ? (what would an item do in a
ledger in such case ?): if no, transaction1 should be incorporated into
item.

keying the relation between actors and transaction1 on a varchar(60)
seems to open the pandora box of data integrity. An explicit (numerical)
key of actor (automatically generated by the database) could be safer.

Transaction2 has city & bank, which I suppose are "on behalf of" or the
opposite ?
Now I wonder about the item: what would be the objects in the following
situation:
Bank A, City 1, Ledger L, an item K is provided by Actor X (btw, date ?)
Actor X goes to City 2, Bank B pays on behalf of Bank A the local
equivalent of item K.
Does Bank B, city 2, Ledger D get an entry ?(item & transaction2 ?)
Does item K of Ledger L be extended with transaction2 ?




-- 
Just because nobody complains does not mean all parachutes are perfect.

Post a reply to this message