Help needed in designing a database

[uttamsaxena]

I want to design a database with following requirement--

1) I am managing my township association which have around 1000 houses.I made a database of each house as its owners name, house no, address, phone, etc.(Single Table)

2) Now I have to maintain the electricity consumption data, and its payment for each house every month and so on forever. I mean every month three data for each house has to be stored i.e. meter_reading, power_consumption and paid_amount.

Can you help me how to build a database for this objective? What table should be made now?

 

[dsdavids]

I would say, to start with, don't include the owner's name in the house table. The owners will change with time, no?
I'd have the following tables:
tblResidence-- Address and House specific info
tblHomeowners--- Names and contact info
tblPowerBill---- Meter Read and amount of bill here.

Consumption would be a calculated value, not saved in a table.

Dean

 

[uttamsaxena]

Thanks a lot for help.


I have a confusion about the meter table.

Will it be like

MeterID / Meter Reading / month

or there be a new reading table for each month. Further the meter for any consumer may change after it is replaced if defective so how this table will be linked.

Pl help.

 

[VicRauch]

The meter table you have shown is not the table Dean suggested. Dean suggested a table (tblPowerBill) to hold the meter reading and the amount of the bill. This table would not have a MeterID field, but rather would have a HouseID field to show which house this meter reading is for. The month field you show in your table will be a date field that should show at least the Month and Year. The amount of the bill would be calculated by taking the difference between last month's meter reading and this month's meter reading, then multiply by the rate, where ever you happen to keep the rate information.
HTH,

 

[Andrew Fergus]

Is it possible for a meter to be removed from one house (assuming it is broken) and replaced with another? If so, that is a layer of complexity you may need to take into account because the new meter will start with a different reading to the old meter.

Also, if you are recording the home owner / occupier details then, as Dean touched on, keep in mind that one home can have many owners and one person can own many houses. So you may need an intermediate table between the house table and the owners table to allow a many-to-many relationship between the houses and their owners. Access doesn't naturally support many-to-many relationships but you can get around this using an intermediate table.

Are you building a billing / receivable database, or as you mentioned in your first post, are you just collecting the meter readings and payment information for each house each month?

Andrew

 

[uttamsaxena]

Hi, everybody,

Thanks for nice tips.

I am not making a billing database as the billing mechanism itself is very complicated. I have just to track the consumption pattern of each household.

Following thing are also to be checked but I am not that advance in Access , so your help would be needed on this---

1. Suppose there are 1000 houses so every month the powertbl will have to be added with 1000 records. What could be the method for this. Is it possible that every month starting 1000 new records are added automatically and only feeding of reading is required.

2. How it could be checked that no meter have duplicate record with different readings for any month.

3. How it could be checked that reading of any meter for each month is never less than the reading of same meter in immediatly preceding month.

Any suggestion is welcome.

 

[Andrew Fergus]

Hi

From where will you source all of this data each month? Does the data already exist in another application within your organisation?

Regarding your latest questions, the prevention of duplicate entries for a household in any one month can be done at the table level - depending on the data captured in the month/year field and what fields you choose to use as your primary key.

To detect instances where meter readings have gone backwards you would need to use a query or two.

Before we get into any specific solutions, I'm curious to know where you will be getting the data from each month.

Andrew

 

[uttamsaxena]

I have taken this as a learning project and to study the annual consumtion pattern for each 1000 establishments comparising mainly homes, shops, hospital, etc in my township. The establishmentwise reading will be given by the power supply utility in printed format i.e Name/meter no./month/reading/bill amt only. I am not interested in bill generation as the tarrif structure is very complex and will need a tailor made software by some company. This is just a project with the aim of Access implementation and observing power usage pattern. Nothing commercial.

Thanks

 

[dsdavids]

Hey, you guys ran ahead while I wasn't looking. I see nobody missed me and everyone has had great input.

I didn't think to anticipate that the meters could change and therefore the readings' sequence could become incalculable.
You'd have to then be prepared as well for the possibility that a meter, once repaired, may be installed on a different house altogether. I can see having a joining table that assigns the status of a meter to a house, or out of service. Then you'd record transfers and such whereby you pull the proper meter to the appropriate bill and vice versa.

I think if you didn't need the meter info, maybe you should just record a starting and ending reading in each power bill record. Or for that matter, record only the number of kilowatts consumed if that is clearly listed for you already in the utility printout. Of course you'd be skipping a lot of potential learning if you slim it down to only what you need.

If you are receiving the information on paper, I suppose there is no alternative other than going down the sheet, entering each record by hand. It will be very important to tailor the input form to be suited for the process. Tab order and input methods, defaults and combo boxes will greatly simplify the process if used correctly and layed out in a way that corresponds intuitively to the layout on the paper printout.
For instance, does your data list go by meter number, location or homeowner? Is all that info readily available in the same table on the sheet? Depending on your answer you'd be basing your recordsource on a query where you select what you do know, usually from a combo box, and pull in the related info and defaults from other tables. I would examine the source and and allow that to drive a lot of the decisions up front as you plan your database.

Dean

 

[uttamsaxena]

The data provided by the utility is in the following format-

Serial No/ Consumer No/ Meter No/ Name and Address/ Meter reading/Meter status i.e. OK, Defective or No reading.

This info is provided for all the consumers for a month in one booklet. Like wise I have booklets for all the months for this financial year i.e. April to Sept.

As for as the meters are concerned following situations are possible-

1. All the consumers have same meter for all the period and in this case it must be checked that reading recorded should not be less than previous month's reading and if this happen reading should be physically verified and rectified in database.

2. Any consumers meter may be defective and a new meter with some initial reading will be installed. In this case the consumption, in the month when meter is changed, will be the :
(New meter reading - New meter initial reading) + (Old meter reading on removal - old meter previous month reading)

3. The defective meter may be scraped if unserviceable or may be installed at same or any other house where its meter becomes defective.

I have not been provided with the kilowatt Hours (i.e. kWh or Units consumed) but the meter reading for each month and kWh is to be calculated by substracting the current reading and previous reading.