Post reply

Simple Junction table design question

  • November 26, 2024 at 8:47 pm

    #4489536

    I have a disagreement with the programming team on an app that I designed. It's not a major disagreement. It's more of a design question.

    The database that I designed has many tables. Two of the tables are:

    • Communities - Which is a list of communities where people can live
    • Persons - Which is a list of people who live in those communities

    Because a Person can live in multiple communities there is a many to many relationship between these two tables. Therefore I created a table called: PersonCommunities that serves as a junction table.

    The PersonCommunities has the following fields:

    • ID
    • CommunityID
    • PersonID

    I was surprised to find that the programmers removed the ID field from the PersonCommunities table. When I asked them about it, they said that the ID field served no purpose. Since I always create tables with an ID field I'm not sure if they are right or I am right. Are ID fields necessary on junction tables?

    Thanks

     

     

     

     

     

    • This topic was modified 1 hour, 5 minutes ago by  eichnerm.

  • November 26, 2024 at 9:53 pm

    #4489565

    ID isn't necessary.  The combination of CommunityID and PersonID should be unique, because a person cannot belong to a community multiple times.  Since you already have a unique key, I see no point in adding an additional unique key, especially an artificial one.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply