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 <[email protected]> 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] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cacw6n4ut06427djkkwedsotm++8pdent8yplxgxj6whuvx9...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
