On 2019-01-26 12:22, Ted Roche wrote:
Adam:
Not a UML expert, but it seems like the restrictions tables has a "Many-To-Many" relationship with Country and Category. By having no unique PK of it's own, it's implied that there is only one record for each combination of country and category. So you can define add, edit, update, delete instructions "WHERE Country=XXX and Category = YYY" which will work all the time. That's proper relational integrity and 4th normal form.
The problem happens if you start adding attributes (fields) to the record where a combination of country/category could have more than one record, say, and agerestriction for gender male but no age restriction for gender female where you now have two records with identical primary keys. Which means they're no longer primary keys, since they do not uniquely identify records. So, you'd have to add another field or two to the composite key, or finally break down and add a unique PK field.
To avoid this kind of refactoring later in the process, my rule has always been that every table has a unique, non-data-bearing PK which uniquely identifies the record from birth to death. You will never have to deal with all the RI code involved in changing primary keys because the data values (category or country codes) change, and avoid intricate and bothersome code.
jomo.
Excellent logic, Ted. Purists be damned! lol