I just went down this road not too long ago. All I can say is; it's nice to be in control<g>.
Strictly speaking, a single table for lookup values is not normalized. What your doing with the procedures below is to move part of the normalization into your code rather then having it in the database design. A shipping type is not the same thing as a Salutation even though both lookups can be represented by the same structure. They are not the same thing and cannot be applied to the same things. My point of view is that we work in the real world and you can take shortcuts to make things easier and faster, but you have to understand the consequences (the use of surrogate keys for example). In this case, if you can *absolutely* guarantee that the data will never be accessed by anything other then your code, then you could live with this shortcut easily. If you cannot, I still think this is a relatively safe thing to do; anyone looking at the design is going to know that they need to build a view to see the data filtered correctly for a specific type of lookup. But strictly speaking, the design is not correct. However it does save a ton of time as you only need one maintenance form to support any number of lookups. But really, the database design should be able to stand on it's own. Nothing external should be required to make it work. Jim. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, July 23, 2007 9:43 AM To: [EMAIL PROTECTED] Subject: [NF] Consulting saga continued or Why Be Normal? Well I have a configuration tool designed for generating data for our sales screen. I was told that our DBA will not allow a single table for lookups. Instead I now need 5 tables to hold identical data. Key, Description, status, ChangeDate, LastUser All I did was add another column for generalized key. Key, GKey, Description, status, ChangeDate, LastUser Unfortunately I need to change all my syntax to adapt to the frigin table and column names. I got the configuration form done in 3 days with lots of side requests. I guess that I can redo it in about the same time. :) so code like this : Public Shared Function ShowNode(ByVal gkey As Integer, ByVal dt As DataTable) As DataTable Dim ds As DataSet = New DataSet Dim sql As String = "Select * from itapps.bizLists where bl_gkey = " & gkey & " and bl_status = 0 order by 3 " ds = DataPortal.FillDataSet(sql, ds, "MasterList", "LOL") dt = ds.Tables(0) ShowNode = dt ds.Dispose() dt.Dispose() End Function It will probably have to get refactored to : Public Shared Function ShowNode( ByVal dt As DataTable, byVal tbl as string, byVal colPre as string, byVal TName as string ) As DataTable Dim ds As DataSet = New DataSet Dim sql As String = "Select * from itapps." & tbl& " where " &ColPre & "_status = 0 order by 2 " ds = DataPortal.FillDataSet(sql, ds, "MasterList", "LOL") dt = ds.Tables(0) ShowNode = dt ds.Dispose() dt.Dispose() End Function I have update, insert, and removal code that all needs to get this stupidity because the term NORMALIZED is a farce here. [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** 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.
