My tables often have some JOINed "code lookup" tables, something like this
CREATE TABLE MyDetailTable
(
D_ID int IDENTITY(1,1) NOT NULL,
D_GroupID int NOT NULL,
D_MemberID int NOT NULL,
D_Col1 int,
D_Col2 varchar(10),
...
PRIMARY KEY ( D_ID )
)
CREATE TABLE MyGroup
(
G_ID int IDENTITY(1,1) NOT NULL,
G_Name varchar(30),
...
PRIMARY KEY ( G_ID )
)
CREATE TABLE MyMember
(
M_ID int IDENTITY(1,1) NOT NULL,
M_Name varchar(30),
M_CountryID int,
...
PRIMARY KEY ( M_ID )
)
CREATE TABLE MyCountry
(
C_ID int IDENTITY(1,1) NOT NULL,
C_Name varchar(30),
...
PRIMARY KEY ( C_ID )
)So then I can JOIN with
SELECT ...
FROM MyDetailTable AS D
JOIN MyGroup AS G
ON G.G_ID = D.D_GroupID
JOIN MyMember AS M
ON M.M_ID = D.D_MemberID
JOIN MyCountry AS C
ON C.C_ID = M.M_CountryIDUntil recently I have had a view for these:
CREATE VIEW MyDetailView
AS
SELECT D_ID AS V_D_ID,
G_Name AS V_D_G_Name,
M_Name AS V_D_M_Name,
C_Name AS V_D_C_Name
FROM MyDetailTable AS D
JOIN MyGroup AS G
ON G.G_ID = D.D_GroupID
JOIN MyMember AS M
ON M.M_ID = D.D_MemberID
JOIN MyCountry AS C
ON C.C_ID = M.M_CountryIDso that whenever I wanted any of the lookup columns I could do
SELECT ...
FROM MyDetailTable AS D
JOIN MyDetailView AS DV
ON DV.V_D_ID = D.D_ID
WHERE D.D_ID = 1234but it seemed to me that this was daft as it references MyDetailTable twice.
So I have changed the view to include all the columns from the parent table too:
CREATE VIEW MyDetailView
AS
SELECT D_ID,
D_GroupID int NOT NULL,
D_MemberID int NOT NULL,
D_Col1 int,
D_Col2 varchar(10),
...
--
G_Name AS V_D_G_Name,
M_Name AS V_D_M_Name,
C_Name AS V_D_C_Name
FROM MyDetailTable AS D
JOIN MyGroup AS G
ON G.G_ID = D.D_GroupID
JOIN MyMember AS M
ON M.M_ID = D.D_MemberID
JOIN MyCountry AS C
ON C.C_ID = M.M_CountryIDso now I can just say
SELECT ...
FROM MyDetailView AS DV
WHERE DV.D_ID = 1234I still use the table name if I don't need any lookup columns, but I used the View name if I want a combination of table columns and view lookup-columns.
Does this seem like a good idea? or are there downsides?
As I see it I avoid having JOINs to all the lookup columns peppered through my code. If something changes (rare, admitedly) I can just fix it in the VIEW. However, there is less chance of my making a mistake in a JOIN as all the logic is encapsulated in the JOIN and in effect re-uses that code each time.
Another useful side effect is that if a column becomes obsolete I can either comment it out in the VIEW (which means that any code referencing it will break), or I can allow backward compatbility by making the VIEW select that column in a forward-compatible fashion.
(Where I comment out a column, to force an error in DEC, I can choose to comment it back in on PRODUCTION "just in case" it is still referenced somewhere, perhaps only in a Client's own query)
. We provide a report / screens where users can review the errors but in practice they never bother. Beats me ... all the errors are things that they told us their other systems don't permit! such as missing State / Postcode (but only in countries where such things are "required") and most of our clients have tens-of-thousands of such errors - management tell me that they are all, indeed, critical and will be cleaned up in the source systems ... it never happens, and yet they continue to make money 
