Help with normalizing a table...

[raged789]

Hi guys. Alright I have been at this for awhile and it seems so simple yet when I think into the logic it wont be normalized. This is a senior project and I thought it was normalized after getting help from my teacher, but after looking at it, it isnt.

An overview:

I am using MS Access.

This is a database about rental houses. It has tables like owner, occupant, Lot, Owner Finance Information etc.

The problem likes with the Utilities Table. In this table I just need to keep track of the ProviderName (such as White River Electric, Verizon etc), and the ProviderType (such as water, electric, sewer, cable etc.)

This table will be branching off of the Lot Table (Lot as in lot of a rental house). The Lot table's PK is LegalNumber (ex: 223 CCL, where 223 is the lot number, and ccl is the subdivision).

A Lot can have many Utilities.
A Provider (ProviderName) can provide many types of services (ProviderType) Example: White River can provide the water and the electric.

Ok so now that the business logic is out of the way. I have tried many ways. I have tried an intersection table which just had UtilityID, and LegalNumber as composite keys. Then I had that branched off to the Utilities table which had UtilityID as the PK, ProviderName, and ProviderType. That wont work because there would be multiple tables of the same stuff when a provider provided more than one service.

The other way I tried was with 2 more tables comming off of the intersection table which didnt work either.

any help would be great and thanks!

[olivthill]

If you have a cartesian mind, you will notice that you can have three situations:

1. One element may not be linked to more than one element, (1:n relationship) e.g. the geometical shape of an object is a circle or a rectangle, but not both.

2. One element may be linked to several elements, e.g. a rectangle is the shape of a door, and is the shape of a bed (n:1 relationship).

3. One element may be linked to several elements, and vice versa (n:m relationship), e.g. a person reads several books, and a book is read by several persons.
In that case, you need to have an intermediary table, in order to reduce the n:m problem to a n:1 problem and a 1:m problem.

I would answer your question with three tables:

- Lot table: Lot ID (=legal number)
- LotUtility table : LotUtility ID (PK), Lot ID, Utility ID
- Utility table: Utility ID, Provider's name, Provider's type

[raged789]

Thanks for the reply. Like I said in my original post, I tried it with an intersection table.

With your answer I still have the same problem of tables with the same data when a provider provides more than one type of utility.

[olivthill]

Finding question is finding half of solutions.

I don't understand your question. What do you mean by "the same data when a provider provides more than one type of utility."

Let's take an example:

Lot table: ID
L1
L2
L3

LotUtility table: LotUtility ID (PK), Lot ID, Utility ID
LU1, L1, U1
LU2, L1, U2
LU3, L2, U1
LU4, L3, U3

Utility table: Utility ID, Provider's name, Provider's type
U1, e1, electricity
U2, w1, water
U3, e2, electricity

You can read that:
- Lot L1 has electricty from e1 and water from w1
- Lot L2 has only electricity from e1
- Lot L3 has only eletricty from e3

Are there any situations that cannot be described with my three tables?

[raged789]

thank you again for taking the time to help me.

What if e1 provides electricity and e1 provides water for the same lot? Would that still work? I am a novice when it comes to normalizing so bare with me.

The interface that I will be using with this will have an option to bring up all the utilities for a particular lot so the user might see something like:

Water-White River Electric
Electricity-White River Electric
Cable-ABCcableCompany
Sewer-White River Electric
Phone-GTE

Another question I have: in your example of the LotUtility intersection table, is LotID and UtilityID a foreign key?

Also is UtilityID just an autonumber?

Let me get this straight, there would be one LotUtilityID to all utilities for a particular lot correct?

thank you again

[olivthill]

Of course, there can be other solutions than mine, and some might be better. Anyway, I'll keep on talking about my idea, because I have not enough imagination to think of something else.

in your example of the LotUtility intersection table, is LotID and UtilityID a foreign key?Yes.
is UtilityID just an autonumber?Yes
What if e1 provides electricity and e1 provides water for the same lot? Would that still work?That's a good question. I did not think of that case.
I would have two rows in my Utility table:

U100, e1, electricity
U101, e1, water

and two lines to make the link with the lot in my LotUtility table :

LU200, L5, U100
LU201, L5, U101

But, my utility table has changed. It does not have anymore one row per utility company, but one row per service. So it should be called a table of services. I've made a mistake when I called it a table of utilities.

And since a table of utilities is needed, then the data model should be changed to:

- Lot table: Lot ID (PK)
- LotService table: LotService ID (PK), Lot ID (FK), Service ID (FK)
- Service table: Service ID (PK), Utility ID (FK), Service type ID (FK)
- Utility table: Utility ID (PK), Provider name
- Service type table: Service type ID, Service type name

(PK = primary key = autonumber (except in the case of Lot ID))
(FK = foreign key)

The interface that I will be using with this will have an option to bring up all the utilities for a particular lot so the user might see something like:

Water-White River Electric
Electricity-White River Electric
Cable-ABCcableCompany
Sewer-White River Electric
Phone-GTEThe query would be, in SQL: SELECT Service_name, Provider_name
FROM Utility_table, Service_type table, Service_table, LotService_table
WHERE Utility_table.Utility_ID = Service_table.Utility_ID
AND Service_type_table.Service_type_ID = Service_table.Service_type_ID
AND LotService_table.Service_ID = Service_table.Service_ID
AND LotService_table.Lot_ID = one_particular_lot

[raged789]

WHEW! Alright I think that I get it now. Thank you so much for all your help!