Database Design

[Jeff Gaines]

I want to write an application to keep tabs on income/expenditure for a
small block of (9) flats.
They are under long leases and there are about 15-20 categories of charges
such as insurance, gardening, drive maintenance etc.
The data is in a spreadsheet at the moment so when a bill comes in it
needs to be categorised and then the costs divided between the flats. The
percentages payable are different for each category.

I am really struggling to turn the spreadsheet matrix into a sensible
database. I have bits of paper all round me but in essence I could have
one table for lease records which, contained everything keyed on flat
number, or a separate table for each category, again keyed on flat number.
I need to allow for start/end dates for the categories as there are
sometimes adjustments made (e.g. a flat opting out of he centralised hot
water system).

Is there a preferred way of turning a matrix into a database or can
anybody point me to some online reading please?

Many thanks.

 

[Mark Rae [MVP]]

Is there a preferred way of turning a matrix into a database

Not in my experience. If I were you, I'd view this as a fantastic
opportunity to design a database from the ground up which meets your current
(and future) business requirements and not try to shoe-horn your existing
system into it...

or can anybody point me to some online reading please?

http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q="Database+design"

 

[Gregory A. Beamer]

I want to write an application to keep tabs on income/expenditure for a
small block of (9) flats.
They are under long leases and there are about 15-20 categories of charges
such as insurance, gardening, drive maintenance etc.
The data is in a spreadsheet at the moment so when a bill comes in it
needs to be categorised and then the costs divided between the flats. The
percentages payable are different for each category.

I am really struggling to turn the spreadsheet matrix into a sensible
database. I have bits of paper all round me but in essence I could have
one table for lease records which, contained everything keyed on flat
number, or a separate table for each category, again keyed on flat number.
I need to allow for start/end dates for the categories as there are
sometimes adjustments made (e.g. a flat opting out of he centralised hot
water system).

Is there a preferred way of turning a matrix into a database or can
anybody point me to some online reading please?

A matrix is a report and has nothing to do with proper database design. I am
sure a tool could be created, but it would paint you into a really tight
box. In many ways, to do it right, it is as complex as a native decompiler.
Ouch!

if you are not familiar with database design, the book to pick up is
Database Design for Mere Mortals. Rather high level, but it has good advice
on figure out what "objects" have to become tables.

In your system, it sounds like you have at least the following "objects"

Rental Units (flats)
Charges
Leases
Renter or Lessee

If you just need a low tech solution, you can create a spreadsheet. If you
need something more, you can certainly create a database. If you do it
correctly, you might be able to sell the idea to others or expand it as your
own needs grow. So, I agree with Mark that this is a prime opportunity to
design the database.


--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************

 

[Jeff Gaines]

If you just need a low tech solution, you can create a spreadsheet. If
you need something more, you can certainly create a database. If you do it
correctly, you might be able to sell the idea to others or expand it as
your own needs grow. So, I agree with Mark that this is a prime
opportunity to design the database.

I've gone for a master lease table which has all the lease details
including the % charges for the 15 categories. The alternative was a table
for each category just containing one entry for each lease. With the low
number of leases I guess there's little performance difference. It was a
choice of writing 15 functions against 1 table or 1 function against 15
tables.

 

[Gregory A. Beamer]

I've gone for a master lease table which has all the lease details
including the % charges for the 15 categories. The alternative was a table
for each category just containing one entry for each lease. With the low
number of leases I guess there's little performance difference. It was a
choice of writing 15 functions against 1 table or 1 function against 15
tables.

I don't know all of the details and I am not sure I would agree with the
solution, but when you are dealing with a fixed, small set of assets,
sometimes it is easier to do what you are familiar with. Ultimately, in
software, the questions are:

1. Does it work now?
2. Will it work for the future?

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************