Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Help with Normalizing..

    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!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I think you're well on your way. What I would add to the above is to break down the tables as follows:

    tblProvider:

    fields: ProviderNum (the PK), ProviderName, ProAddress, ad amnauseum

    This table would house all available providers, and all information about them.


    tblServices:

    fields: ServiceNum (the PK), ServiceType

    This table would house all necessary services. This can get as detailed as necessary. Say you have two phone service providers, one for local service and one for long distance. They would have seperate numbers and seperate service types in your table.


    tblValidProviders:

    fields: ProviderNum, ServiceNum

    This table would govern the valid combos. If a combo is not in this table, you must consciously add it to the table or it's simply not available, such as the gas company providing telephone service.

    Is this helpful?

    Sam

  3. #3
    Join Date
    Oct 2005
    Posts
    4
    thank you for your reply!

    So with:

    tblValidProviders:

    fields: ProviderNum, ServiceNum

    This is the intersection table correct?

    Also, since this table will be comming off of the Lot table, should we put LegalNumber(Lot's PK) in there as a FK?

    Are ProviderNum and ServiceNum composite Pk's in this table?

    Now qeustions with the other tables:

    Then tblServices, and tblProviders branches off the ValidProviders correct?

    What if a provider provides more than one type of service such as electricity and water?

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I Like Sam's solution.

    Quote Originally Posted by K-Dubb
    So with:

    tblValidProviders:

    fields: ProviderNum, ServiceNum

    This is the intersection table correct?
    I suppose that's a way of thinking of it. It's the table that says "this provider provides this service". So I'd think of it as "ValidContracts" (I'll come back to this!)

    Quote Originally Posted by K-Dubb
    Also, since this table will be comming off of the Lot table, should we put LegalNumber(Lot's PK) in there as a FK?
    Intersting question! I think the answer is no. This table is really a list of possible contract combinations available for each service. And you really need to keep the table as it is so that you can manage discrete contract options.

    Adding to Sam's solution, I would create another new table as follows:

    tblLotUtilContracts:
    fields:
    LOT (PK)
    ProviderNum (PK/FK)
    ServiceNum (PK/FK)

    This table represents the list of utility contracts that a Lot has. But because it's related to the tblValidProviders table (ie. valid contracts) through the FK you are limited to only enter valid contracts (and you could set up a pair of combos to facilitate this given the design i.e. drop-down water and then the other combo lists only providers of water.

    This all might seem over complicated but your problem is a complex one and describing it in this systematic approach avoids lots a problems.

    Quote Originally Posted by K-Dubb
    Are ProviderNum and ServiceNum composite Pk's in this table?
    Yes (providing you don't add LOT).

    Quote Originally Posted by K-Dubb
    Now qeustions with the other tables:

    Then tblServices, and tblProviders branches off the ValidProviders correct?
    Yes.

    Quote Originally Posted by K-Dubb
    What if a provider provides more than one type of service such as electricity and water?
    You will have two records for that supplier in the ValidProviders table (better to think "valid contracts").

    ProviderNum, ServiceNum
    White River Electric 2 (water)
    White River Electric 3 (electric)

    Make sense ?

    Chris

  5. #5
    Join Date
    Oct 2005
    Posts
    4
    thanks for the reply. I am a bit confused. So now with the addition of tblLotUtilContracts, we have 4 tables?

    I am having trouble seeing how they are all connected.

    Lot:
    LOT (PK)

    tblLotUtilContracts:
    LOT (PK)
    ProviderNum (PK/FK)
    ServiceNum (PK/FK)

    So this table branches off of the Lot table via Lot's PK?

    Then how does tblLotUtilContracts connect to tblValidProviders?

    Since tblValidProviders has ProviderNum and ServiceNum, and so does tblLotUtilContracts, which one do you connect the tables with because you cant connect both like this: ProviderNum.tblLotUtilContracts to ProviderNum.tblValidProviders, and ServiceNum.tblLotUtilContracts to ServiceNum.tblValidProviders?

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Sorry it's been a few days. I've been real busy.

    Quote Originally Posted by K-Dubb
    thanks for the reply. I am a bit confused. So now with the addition of tblLotUtilContracts, we have 4 tables?
    Correct. Take a look at my example attached.

    Quote Originally Posted by K-Dubb
    I am having trouble seeing how they are all connected.

    Lot:
    LOT (PK)

    tblLotUtilContracts:
    LOT (PK)
    ProviderNum (PK/FK)
    ServiceNum (PK/FK)

    So this table branches off of the Lot table via Lot's PK?


    Then how does tblLotUtilContracts connect to tblValidProviders?

    Since tblValidProviders has ProviderNum and ServiceNum, and so does tblLotUtilContracts, which one do you connect the tables with because you cant connect both like this: ProviderNum.tblLotUtilContracts to ProviderNum.tblValidProviders, and ServiceNum.tblLotUtilContracts to ServiceNum.tblValidProviders?
    Why not ? That's exactly what you do. Assuming you are using the relationships window, create the first relationship (drag drop etc), then in the edit window, add the second relationship. Note you'll probably have to do the drag drop from tblValidProviders to tblLotUtilContracts because otherwise Access won't be able to determin the PK.

    hth
    Chris
    Attached Thumbnails Attached Thumbnails rel1.jpg  
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2005
    Posts
    4
    thanks a lot howey, you have been a great help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •