Debating a coworker about a table schema in SQL server. The debate is about a parent child relationship. Should the child have its own identity?
Here is the situation (in a generic way).
PeopleTable (a proxy of a table in another db)
- PersonID PK
-PersonID FK PK
-PlanYear FK PK
-other details columns
I am of the mind that the third table should have its own ID rather than the combination PK that is also an FK from two other tables. Even though there will only be one instance per person, per year.
Here's my rationale -
the specific plan is its own entity, even if it can only relate to the specific person and year. SQL Server needs to work harder to order by two columns also to join on two columns (as so users running queries). And if you ever want to create a rest API, what does the URL look like?
The other side of the argument is rooted in difficulties encountered with a database that created its own local ID for referencing entities from another db.
-bucketId FK PK
this was done because the droplets in main are partitioned per bucket
Bucket 1 - Droplet 1,2,3.
Bucket 2 - Droplet 1,2,3.
so that there is no way to refer to a droplet without referring to the bucket as well. So this schema was born of another schema and was an attempt to simplify referring to a single droplet entity in the db.
there are other structures that can make our database more open to future change. EAV for the plans would be a great option! Plans change, new ones are added. EAV would handle this beautifully.
Of course in this case the plans will not change that often or by that much, so EAV is un-necessary since it adds complexity.
In any case, my preference is to have an ID for each table. The PersonID and Year are still necessary and can be used for identifying the relationship. But in this way the plan has its own identity. Other entities relates to the plan itself can refer to the plan directly.
Guess we will see how it goes.