Stylistic question about table relationships
I have a number of tables, all of which have a char(4) code. In one
instance it is indicating a status flag for a particular case record (eg
Open, Closed, Pending etc); in another it is indicating the type of quick
lookup function (eg in History, or Diagnosis etc).
At the moment, for each type of code I have defined a table containing all
the possible code values, and long text to which I have related the tables
to ensure that only valid code entries are possible.
Table CaseCodes contains ("OPEN", "Case open"), ("CLOS", "Case closed" ),
( "PEND", "Case Pending" )
Table Case contains a "CaseCode" (foreign key) amongst other fields
Table LookupCodes contains ("HIST", "History lookup"), ("DIAG", "Diagnosis
Table Lookups contains a "LookupCode" (foreign key) amongst other fields.
It occurs to me that I could have one central dictionary of these codes, and
instead of using Sql built-in referential integrity, I could force it using
* Easy joins
* Built in primary key/foreign key constraints
* Lots of trigger code to be written/maintained
* Difficult for user written joins
* Single table easier to administer/code for at the front end
Does anyone have any feelings about this/come across anything similar? Any
advice? (This is a long term project so I want to get my design just
right). Is it possible to have constraints against views to a single table?
(and in which case, is this a good solution)
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL