Re: FK -> non PK - bad design?

Can I assume that SERIAL is some kind of proprietary, non-relational vendor flaw that Newbies always ssem to stick on tables as keys because they don't know what a key is? This is NOT a sequential file system!! This is why you are getting screwed up -- you don't know what a key is.

Why does the account identifier change names from "account_id" (correct) to "id" (totally useless and in violation of ISO-11179 Standards); likewise "type" is not the name of a data element, it is a modifier looking for a noun. Do you know what a data dictionary is? Why is the Service plans table named in the singular? Do you really have only one of them?

CREATE TABLE User_Info
(account_id INTEGER NOT NULL

          REFERENCES Accounts(account_id)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
 plan_type INTEGER NOT NULL 
           REFERENCES Service_Plans(plan_type)
           ON DELETE CASCADE
           ON UPDATE CASCADE,

 CREATE TABLE Service_Plans
 (plan_type INTEGER NOT NULL PRIMARY KEY,  ... );  

>> I was told that this design, where 'plan_type' FK in user_info
table points to a column in service_plan that is _not_ a PK, is bad. <<

The guy that told you this is simply dead wrong. But that is not the problem; the problem is you have is that there are no keys in the schema. A key is subset of attributes in a LOGICAL data model, not a weirdo number that is determined by the PHYSICAL storage used. Received on Wed Apr 09 2003 - 17:55:35 CEST