Re: ERD with a table with strange keys
On Mon, Jan 28, 2019 at 8:34 PM Gene Wirchenko wrote: > At 09:22 2019-01-26, Ted Roche wrote: > > [snip] > > >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. > > Why not? Why not what? When I said "RI code" I was referring to the Relational Integrity code that has to be generated to handle the situations where the Primary Key changes. If any PK changes or gets deleted, you have to decide if the change needs to be Cascaded to other tables where the PK is exported as an FK, or Nullified to remove the FK reference, or prevented if a key change should not be allowed to cascade through the the database. Needless to say, in a complex ERD, these RI rules add another layer of complexity to the application-to-business-object-to-data-manager-to-database path. Instead, an unchangeable PK removes this concern: If the PK never changes, no RI code, and Bob's your uncle. What if someone creates a second restriction row with > the same category, country, and any other factors? Couldn't this > create an integrity nightmare? > This isn't an RI issue, strictly speaking. Relational Integrity concerns the consistency of primary keys and their foreign key representation in associated tables. This is a problem with candidate keys. If there is a situation where there is a candidate key (one or more fields which SHOULD uniquely identify a record. A candidate key can be the primary key (all primary keys are candidate keys, only one candidate key can be the primary key.), Then there is a requirement that the candidate key be defined with a 'unique constraint" key definition in SQL. (Sadly, this is one of the few places that FoxPro clashes with standard SQL in re-using a term with a different meaning: "UNIQUE" is a feature of old, old XBase which SHOULD NOT BE USED. Instead, define the candidate keys with the clever option of "CANDIDATE" which will enforce uniqueness at the table level. Your table handling logic has to catch candidate violation errors. This keeps evil-doers from messing with your data via Excel. Of course, in your application either at the front end or in the business objects (or both!), you should be checking for duplicate records where there should be none and informing the operator a record already exists (and, typically, having them update the existing record instead. Or overwriting, depending on your app's behavior and business rules.) In my client billing app, I have a few tables that have a date > range for when each row is valid. This does mean that I have to > handle the lookup into these tables. > A unique, non-data-bearing primary key solves the RI problem, but candidate keys are pretty difficult to manage when the uniqueness of a candidate key has to include no overlap in the ranges of valid dates in two fields. I'm not aware of a general data pattern that solves all the permutations of this. Modeling data over time introduces some tricky issues. I've run into this in pricelist tables where there are current values and upcoming price changes and so forth. How you structure this seems to be dependent on how you need to access historical or future values; in most cases, I migrate old values into audit tables (write-only) for historical reference. Most of my data models are designed to reflect the current conditions of the data (prices, statuses, etc.) but sometimes I (or the operators) have to get "clever" to close out old orders or schedule future orders when the prices or schedules shift under our feet. This is one of the reasons that data is sometimes duplicated in a database design without it being a violation of normal design: a company record has a shipping address, kept up to date. The shipping address is also copied onto each order, because the two addresses are not the same thing: the first is the company shipping address TODAY, which the order's address is where that address was to be shipped when the order was placed. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- ___ Post Messages to: ProFox@leafe.com 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/CACW6n4uRLc9-ZpkHN2bQ_RQM6uGtKYQp_GfRTmHdJjeAtyaw=a...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or
Re: ERD with a table with strange keys
At 09:22 2019-01-26, Ted Roche wrote: [snip] 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. Why not? What if someone creates a second restriction row with the same category, country, and any other factors? Couldn't this create an integrity nightmare? In my client billing app, I have a few tables that have a date range for when each row is valid. This does mean that I have to handle the lookup into these tables. [snip] Sincerely, Gene Wirchenko ___ Post Messages to: ProFox@leafe.com 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/93ea9c5e198e85a53f2a9cfd7ba03e99@mtlp85 ** 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.
Re: ERD with a table with strange keys
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 ___ Post Messages to: ProFox@leafe.com 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/a0d763ea24d1860e86ea3b6f343c2...@mbsoftwaresolutions.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.
Re: ERD with a table with strange keys
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 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<> > title > catagory <> > > > customer > -- > personID <> > forename > country <> > > > restrictions > --- > country <> > category <> > 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: ProFox@leafe.com 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.
ERD with a table with strange keys
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<> title catagory <> customer -- personID <> forename country <> restrictions --- country <> category <> 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. ___ Post Messages to: ProFox@leafe.com 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/cwlp265mb0099a2ab7ea020cc04529251a6...@cwlp265mb0099.gbrp265.prod.outlook.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.
