Results 1 to 12 of 12
  1. #1
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25

    Many to Many relationships. Floors to buildings?

    This is a simple question for most, but it has me pretty confused. A building can have multiple floors, but a floor can only have 1 building. Correct? However, many buildings have a first floor and "first floor" can appear in many buildings. Is tblBuildings to tblFloors a many to many relationship or is it a 1 to many relationship?

    Thank you,


    Jonathan

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Can a building have many floors? Yes.
    Can a floor have many buildings? Yes.
    Looks like many-to-many.

  3. #3
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Thank you for your reply. Can a floor really be related to many buildings? The first floor in 1 Main Street can not also be found at 2 Main Street, even though 2 Main Street may very well have a first floor with entirely different properties. Perhaps I am over thinking it. When considering many to many relationships, is it a physical real life relationship, or is the determination based on the value in the field. For example the text "1st Floor" can also be used in many buildings, but the physical floor is not found in many buildings. Sorry if I am complicating this.

    Thank you.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Jonathan,
    Perhaps you could tell us more about your proposed database and how you plan to use the info.

    Not all buildings will have a 10th floor, so somehow you identify a Bldg and its Floor(s).

  5. #5
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    My database is just to inventory equipment by location such as what room, what wing, what floor, what building, what property, for multiple properties with multiple buildings. Then I want to be able to quantify the equipment as a total for the entire portfolio of buildings, or as a total for all the building on a property, or a total for 1 building on a property.

    Thank you

  6. #6
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    I think I might be going about this all wrong. Currently, I have a separate table for each Properties, Buildings, floors, areas, and rooms. Maybe I should only have 1 table called location with 5 fields, instead of 5 tables. Then I could have a table for equipment, resulting in just 2 tables. Does this make more sense, and is it still a good database design? I am not sure when a field warrants a table of its own.

    Thank you,
    Jonathan

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Sometimes it makes things clear if you describe "a day in the business".
    In your case, it seems:

    You have an inventory of Equipment. Each piece of equipment has an identifying label. This inventory is located in a number of Locations. A Location could be a Building, floor and room. You want to identify the current location of each piece of equipment. You may want to identify each piece of equipment by TypeOfEquipment (Printer, Table, TV....).

    Here is a free, generic data model that may help with tables and relationships. You may substitute Location for Employee in the model so it makes more sense in your set up.

    Good luck.

  8. #8
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Thank you Orange,

    I will give it a try. Have a great day.

    Jonathan

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Many-to-many...
    You have a table of Floors, maybe B6 thru 145.
    You have a table of buildings.
    A specific floor in a specific building is defined via a junction table that points to a building and a floor. Many buildings in the junction table can point to 1 in the floors table, and many 1's in the Floor table can point to any number of buildings. Thus, many-to-many.
    The floor-building relationship only exists in the junction table. If you want that particular floor-building combination to specify additional data, you could put that in the junction table record.

  10. #10
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    davegri,

    Great advice! My question is answered. This is a great core principle to understand and I will be able to build upon it. I just have to think more about how the data relates to other data, and not pay so much attention to how tangible property relates to other tangible property.

    Thank you!
    Jonathan

  11. #11
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    a floor CANNOT have many buildings.
    many buildings do have the same floor#, but not the same floor.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    a floor CANNOT have many buildings.
    many buildings do have the same floor#, but not the same floor.
    Ah, let's not waste time on semantics...
    I think my explanation speaks for itself.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  2. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  3. Replies: 1
    Last Post: 03-05-2015, 11:27 PM
  4. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  5. one-to-one relationships
    By bonecone in forum Database Design
    Replies: 3
    Last Post: 01-25-2012, 02:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums