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.
On Sat, Jan 26, 2019 at 2:55 AM Adam Buckland dev@thebucklands.co.uk wrote:
As I said previously I have to do things as my lecturers want until June 8th so Advanced Databases, creating a model for international crowdfunding..
I have the following three tables:
Project
projectID <<Primary Key>> title catagory <<Foreign Key>>
customer
personID <<Primary Key>> forename country <<Foreign Key>>
restrictions
country <<composite Primary Key>> category <<Composite Primary Key>> ageRestricted genderRestricted
My lecturer is saying that the composite primary key must be accessible without needing reference to two tables..
In ERD/UML is there anything wrong with having a look up table referenced from two other tables?
Thanks for any pointers google has let me down as has the databases groups on facebook...
Adam.
[excessive quoting removed by server]