locked
Junction Tables RRS feed

  • Question

  • I've a design issue, yet again. I've two tables with many-to-many relationship, where number of activities could be done on an equipment, and similarly equipment might also be treated under certain activities. Junction table between those tables comes up with different set of requirements for each type of activity, on each equipment.

    In attachment 1, I've created my situation where I have Activity and Equipment table and the Junction table between them. Now there are several different kind of activities and each activity appears to have a different set of attributes for the Junction table. This is what I've tried to described in attachment 2.

    If I try to create a different Junction Table for each different activity/equipment combination, then there will be several Junction Tables connecting to the same Activity and Equipment table. Will it be a correct approach according to database design rules??

    I am wondering what are the other possibilities to deal with such situation, if above approach is not correct?? I've tried several approaches but in every approach there is something which makes me turn it down.

    Each activity is later saved in History Table on daily basis to get the progress graph, as hinted in attachment 1. This will later be verified weekly from history table and any excess or missed quantity will be adjusted accordingly. I'm mentioning this because when it'll be verified, I'll then need to go back and make modifications in relevant tables via some automatic procedure according to adjusted amount. I'm mentioning this because maybe it'll have an impact in a way i am trying to design a database.

    I'm really in need of bit of a quick help here with very limited time left in project completion. Any help will be appreciated.

    Thank you,
    K

    Thursday, October 29, 2015 12:22 PM

Answers

  • The junction table can be logically expanded into a category-subcategory design, where the attributes common to all ActivityEquipment are contained in the top-level category table, with separate child tables for each specific attribute subset (the subcategory tables). There should be an attribute in the top-level table indicating which subcategory applies to a particular row.

    This can turn into a lot of tables when you implement the design, and a perfectly acceptable alternative is to include all possible attributes in the single ActivityEquipment table. In that case you might add rules to enforce which attributes must be null, can be null and cannot be null depending on the appropriate subcategory type.


    Paul

    Thursday, October 29, 2015 12:53 PM
  • I am wondering what are the other possibilities to deal with such situation, if above approach is not correct?? I've tried several approaches but in every approach there is something which makes me turn it down.

    Hi KhurramKZ,

    I do not know whether the Attributes relate to the Activity, or to the Equipment.

    When the Attributes are not related to both, you can make a junction table combining Activity_id, Equipment_id and Attribute or Attribute_id. This latter depending on whether the Attributes are listed in a table (Attribute_id) or are "free to fill in".

    If the Attributes are related to the Activity, then you can make an Activity_Attribute table, and use a junction table combining Activity_Attribute_id and Equipment_id.

    In the same way, if the Attributes are related to Equipment, use an Equipment_Attribute table, and a junction table combining Equipment_Attribute_id and Equipment_id.

    Imb. 

    Thursday, October 29, 2015 1:31 PM

All replies

  • The junction table can be logically expanded into a category-subcategory design, where the attributes common to all ActivityEquipment are contained in the top-level category table, with separate child tables for each specific attribute subset (the subcategory tables). There should be an attribute in the top-level table indicating which subcategory applies to a particular row.

    This can turn into a lot of tables when you implement the design, and a perfectly acceptable alternative is to include all possible attributes in the single ActivityEquipment table. In that case you might add rules to enforce which attributes must be null, can be null and cannot be null depending on the appropriate subcategory type.


    Paul

    Thursday, October 29, 2015 12:53 PM
  • Thanks, at least it has given me initial idea.

    Thursday, October 29, 2015 1:25 PM
  • I am wondering what are the other possibilities to deal with such situation, if above approach is not correct?? I've tried several approaches but in every approach there is something which makes me turn it down.

    Hi KhurramKZ,

    I do not know whether the Attributes relate to the Activity, or to the Equipment.

    When the Attributes are not related to both, you can make a junction table combining Activity_id, Equipment_id and Attribute or Attribute_id. This latter depending on whether the Attributes are listed in a table (Attribute_id) or are "free to fill in".

    If the Attributes are related to the Activity, then you can make an Activity_Attribute table, and use a junction table combining Activity_Attribute_id and Equipment_id.

    In the same way, if the Attributes are related to Equipment, use an Equipment_Attribute table, and a junction table combining Equipment_Attribute_id and Equipment_id.

    Imb. 

    Thursday, October 29, 2015 1:31 PM
  • Thank you, I'm certainly expanding my design knowledge here. Attributes are related to Equipment in my case, and this is a new idea for me. I'll work on it in the morning and see.

    I've another design idea coming in and that is, instead of making a Junction Table Activity_Equipment, I create Equipment table a sub-table of Activity Tables since every Activity is going to be done on some equipment. And then make each ActivityType a new table and each ActivityType table will be a sub table of Equipment table (In my first original question, ActivityType is a Junction Table).

    But then, is there going to be a Junction table between Activity and Equipment table... because 1 activity could be perform on several equipments, and similarly each equipment might have several activities? Or there might be no Junction table, since there's no common attribute??
    And then each ActivityType table could be used for several equipments, but since each ActivityType is a separate table, there's no need for Junction tables?

    Thursday, October 29, 2015 3:28 PM
  • Hi KhurramKZ,

    Whether to use Junction tables depends on your requirement. In my option, Junction tables usually are used in many-to-many relationships. In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B.

    You could refer the link below for more information about defining relationships between tables.
    # How to define relationships between tables in an Access database
    https://support.microsoft.com/en-us/kb/304466

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 2, 2015 3:13 AM