Relationships Question from a absolute begginer

[Guido Meng]

Hi everyone!



I'm building my first database and I so far, I've created a bunch of tables with different fields to go with them. I think I've stumbled on the first decision to which I'm not sure what the right path would be to follow. What I'm attempting to do is this:

1) I'm building a database to keep track of the costumers' contact information, and charges of a water distribution company.

2) I have a table that keeps the ServiceID (Primary Key) and the address of the place to which the water is delivered.

3) There's another table that keeps the Info the costumers. The fields are: CostumerID (Primary Key), FirstName, LastName, InitialDate (the date they became costumers, for example when they bought the house), EndDate (the date they sold the house).

Even though it may be a little unlikely for the situation to arise, I would like to add the flexibility to accomodate the possibility of the person A to sell the house to the person B and then some time later, the person B to sell the house not to a person C (completely another invividual) but again to person A (the original owner). My dilemma here is to decide whether to create another CostumerID (for the person A who's rebuying the house) or to build another table in which I'd include the CostumerID as a foreing key and have other fields for the Initial Date and End Date (the period(s) that the person A might own the house). I'll like to build the same flexibility for the contact info, billing info and so on (because this kind of info it's even likelier to change with time).

Thanks a lot for any help!



4) I'll build another table to keep the costumers' contact information. The fields will be: Id_Costumer (foreign key to link the info to the constumers' table), Type of contact (House phone, work phone, cell phone, email, etc) to be chosen from a drop down list (from a Type of contacts table)

[June7]

Do you mean 'customer' instead of 'costumer'?

Think I would take a different approach. Why keep history of the residence ownership? Can a customer have more than one delivery address? Can delivery address be different from billing address? Could just put both address info in the customer info table, yes, sometimes there will be blank fields but that can be tolerated. It is a balancing act between normalization and ease of data entry/output.

If you want to keep all addresses in a separate table, then when new customer signs up, pick address from table. If address not there enter new record. When customer terminates service, can disassociate delivery address which is then available for another customer. Or if customer just changes address because of move then associate new address with customer.

In other words, tie service to customer, not building. It is, after all, the customer who pays the bill, not the building. If a customer returns then resume service with the same customer ID, regardless of what building they are in. This will maintain continuity of customer obligation data.

If you really must have history of customers served at same address then will definitely need junction table with date fields.

[Guido Meng]

Hi June7!

Thanks a lot for the advice!

I'll admit that your approach seems easier and neater specillaly when it comes to the relationship between service-customer (that's the word I intended to use but my english has become a little rusty hahaha) instead of the relashioship service-building. I at first thought of using the former approach but since the variable that remains the same in this particular case it's the building (owners come and go but no matter who's the new owner, the building will always need water to be habitable.) I decided to use the latter approach. I think I'll follow you advice though. I'll like to know your opinion, on what's the best way to handle the possible changes in the billing data for each customer (that really happens quite often in this particular situation as some users sometimes ask to be billed with different data (let's say that some of them have up to 3 different billing sets of data and that they interchange those up to 3 or 4 times a year to their convinence).

One last thing. I've not yet come to the following point in the development of this database but one thing I'll like to do is to calculate the amount of water consumed each month for each customer. I have the info of every reading of each water meter on a monthly basis. To calculate the water consumption for each month, all that is needed is to substract the penultimate field for the water meter reading from the last reading of said water meter. To my surprise, it seems that there's no easy, built-in way in access to reference the prior record once a query it's been done. Maybe I misunderstood this but, if it's not much trouble, I' like to know if this is true. Up until now, I've used Excel a lot for things like the one I just described and to clarify my question, I'll like to know if it's possible to reference a prior record with the equivalent of, for example, AmountConsumed = .Cells(i,5) - .Cells(i-1,5)

Thanks again!!!

[June7]

My misunderstanding - by 'water distribution' I pictured bottled water supplier but you mean a water utility that pipes water to buildings and meters use? Utility company database structure is one I haven't worked with. Depending on the geographic scope of your service, might outgrow an Access database rather quickly. I dout any relatively large utility company would depend on Access database.

I expect utility billing would be invoiced by building and the mailing address for the billing would be the customer account currently associated with the building. By 'billing data' do you mean billing address? Maybe just put addresses in Addressess table and the address record ID as foreign key in CustomerInfo table.

One way to get value from another record is by subquery. Review http://allenbrowne.com/subquery-01.html

[Guido Meng]

Hi again June7!

I've just briefly looked the info of the link you provided and it seems to be exactly what I need, Thank you very much! I'll study it in detail later, this is not my day job (hahaha) so I'll look at night.

One more thing though, since you mention capacity, I'll like to know you opinion on whether access might be the right tool to use for this application. The company for which I'm trying to build this database for it's a rather small one. Currently it has about 178 users and it will never have more than about 225 tops. For each service there will be the address field, the monthly water reading (so there will be about 200 new readings each month), the contant info for each custumer (phones, e-mails, etc), and for each month and for about 10% of the users, some extra data will be needed (extra charges for excess of consumption, repairing, extra charges for overdue payments and so on).

Again, thank you very much for your help!

[June7]

That is a small customer base, as compared to thousands and up, and Access might be adequate for quite a few years. Access has 2gb size limit.