November 26, 2024 at 8:47 pm
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:
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:
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
November 26, 2024 at 9:53 pm
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
November 26, 2024 at 10:09 pm
Yep, definitely remove ID. The key to a "relationship" table should be the relationship keys, NOT an ID column.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 26, 2024 at 10:10 pm
They are probably right. There is probably no value in that column (not to mention the ambiguous "ID" name) unless you are using an ORM or other tool/framework that insists on a single-column primary key.
There is a popular tendency to throw an identity key ID column on every table. That can be great for performance, and even efficiency of writing queries, when there is no efficient natural key (one or few "small" columns -- not big strings, nor even many otherwise "small" integer columns). Joining on a 4-byte integer is much more efficient than joining on a 100 byte string or a dozen 4-byte integers.
Other than the cases mentioned above (ORM/framework requirement), an identity-key id may be useless in terms of performance for junction tables where the only joins or filters are on the primary keys of the tables being joined or perhaps other related columns, and that identity key column is never referenced. Or on a lookup table where the natural key is a tiny string -- e.g., U.S. state abbreviations (which are only two bytes char, and rarely change over one or more lifetimes).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply