May 4, 2011 at 8:26 am
Good day all,
I "think" I have a fairly good grasp of database design, but this particular issue has me at a cognitive dissonance with myself. Sparing all the thoughts and debate, here is my scenario. I have an "audit" table. This audit is used for things internal to the business and is more or less a "tracking" system for the issues we experience in manufacturing to a standard. The audit has very few free form text fields most of it is lookup type stuff. My issue is this, I have read time and time again it is highly frowned upon to combine multiple lookups into one table. However, I also see at some point in that discussion where those lookup values are "infinite" and varying in type. My situation and where the dissonance comes in, is that all the lookup values will NEVER exceed 20-25 characters, and there are approximately 10 "lookup-types" that all variable length fields. It is possible that someday one would go over 25 but its is very unlikely. Any "field" that cant describe its purpose in 20-25 characters at that point should be a free form text box IMO, not a "lookup value". But I digress, my real question forum is how should I proceed. Im currently upgrading/rewriting the app and would like to know if my design theory is off.
To recap, I have a OTLT that has values for approx 10 fields. Here is my structure:
CREATE TABLE [dbo].[DrpDwnData](
[ID] [int] IDENTITY(676,1) NOT NULL,
[Item] [varchar](50) NOT NULL,
[Category] [varchar](15) NOT NULL,
[Attribute1] [varchar](10) NULL,
[IsActive] [bit] NULL
CONSTRAINT [PK_PAF_DrpDwnData] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The values stored in the "audit" table are the PKID from this table, and when I call the form Im filtering the 1 dataset for each drop down box, to populate the values. I also believe I can enforce integrity at some level in both the DB and the application by doing this.
I know they say to have a 1:1 for each lookup field, which I have done for things more dynamic like user names, product numbers, but the rest that are in the "common" table are like I said, more finite and rigid. They do add to it once in a while but its infrequent. And by having an active field I never delete data, so fkey issues can be limited to a smaller set of "what happened" situations. Knowing that my data is finite and under 100 values for each type, is it a good design to move forward with?
May 5, 2011 at 1:39 pm
IMHO, I think using one table for all lookups gets real ugly real fast. I'd much prefer having a separate lookup table for each lookup. It is easier to maintain and understand, especially as new developers come on board.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 5, 2011 at 2:09 pm
Thanks for you insight Mike. From the title of the article I struggled with the thought. So much so that I thought it best to at minimum seek others opinions. Its difficult to hash these things out when you dont have other people around you to bounce this stuff off of.
The app has survived the last 4 years with it the way I described. I decided before I go into a rewrite, it was time to seek other's opinions. I have had very little issues with the app, outside of not using 1NF in the very beginning. I quickly fixed that after a few requests to make changes to the lookup data. So beyond that it has worked out well, but I havent lived in this world "DBA/Programmer" long enough to see a snowball coming at a distance.
Thanks again for your input.
July 22, 2011 at 5:00 am
I realize that I'm rendering an opinion probably too late, but here we go.
The app has survived the last 4 years with it the way I described. I decided before I go into a rewrite, it was time to seek other's opinions. I have had very little issues with the app, outside of not using 1NF in the very beginning.
Like others, I agree that OTLT's are something that shouldn't be designed into a database. That, notwithstanding, the app has worked quite nicely for you over many years.
Unless you've had performance problems or a real analysis of the app says that it will have performance problems in the future due to increased scale, I wouldn't touch it. The OTLT certainly wasn't the best solution someone could design, but it's been working successfully. Changing it may be like poking at a Cobra with a very short stick. 😉
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply