normalizing tables

[desertdirk]

I am relatively new at this, so I have been reading everything to the point I have gotten confused. My situation:

I want to create a utility billing application for an RV campground. Electric meters are read for each site, once a month and the tenant is billed. So, I have a table for tentants:
id (autonumber)
fname
lname
sitenumber

should I have one table for sites (there are 30 sites) or should I have a table for each site? (30 tables)since a reading will be done each month for each site. I am not sure where to add the meter readings for each month, certainly not to the tenants table.

And when calculating the monthly usage and amount to bill (current month reading-previous month reading * rate)should this be done in the query or on the form?

Thanks a bunch

 

[JeremyNYC]

Dirk,

First off, let me recommend another bit of reading, in case you haven't come across it: Paul Litwin's Fundamentals of Relational Database Design. There's a copy on my website.

Next, put all of the sites in one table. Each of your tables should model a type of entity. You showed us one that models a person, telling us their name and where their RV is. The next table should model a site. You have one table for all people, right? Similarly, you'll want one table for all sites, with a field to differentiate between them. Actually, you'll probably have two fields that differeniate--an autonumber, because it's easy to use and relate to other tables, and a field that records how humans differentiate between the sites. This may be a number, a name (Shady Grove, Lion's Den), or some combination (13 Inner Loop).

The billing gets a little more complicated. You're going to want to be careful that changing the rate at which you bill for these services doesn't mess up all of your historical data. In most billing systems this means storing the rate in one table and the amount used and the rate charged in another table.

Here's my guess at some of the tables you'll end up with (there are sure to be other tables and other fields within these tables):
tblPerson
(you showed us this one)

tblSite
SiteID
SiteName

tblSiteCharges
SiteID
DueDate
MeterReading
RateCharged
PaidDate

tblConstant
Rate

tblConstant is something that I use in most databases. I've just now realized that it's a bit of a misnomer, as the values stored here are not in fact constant, they just don't change very often. I use this to store all sorts of data that I'll use throughout the database, so I only have to update it in one place.

The querying to generate the bills will be rather complex, as you'll have to grab multiple records and do some subtraction. I've done this before, but not for a very long time. I found a method for doing it on comp.databases.ms-access. You can comb through the archives on groups.google.com. But first get the structure up and running.

Hope this helps.

Jeremy



==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[desertdirk]

Jeremy
thanks for your insight. I like the idea of a rate table but in my situation, the rate changes on a monthly basis determined by 3 main meter readings.

I will use the site charges as you suggest, I like that as well.

I guess I will just have to muck around a bit and try things.

Thanks again
DD