All: I wanted to solicit some opinions about how best to layout a database table for a project I am working on. I have a distributed application that reports run time information back to a central machine. Included in the report are the parameters used during runtime... approx 25 or so. Parameters are site specific and may be changed locally by whomever runs the application. I have a table that records an application instance (columns such as location, version etc), and a table that records report instances (time, date, size). I wanted to be able to query at a glance the most recent parameter set in use, so I came up with the following table design...
Design A) primaryKey | appInstanceId | reportId | param1 | param2 | ... | param25 I spoke to a few people at work, and they all say the same thing... "this isn't normalized'. I get the following suggestion: Design B) primaryKey | appInstanceId | reportId |paramNum |paramValue ---with a table second table-- paramNum | paramDescription The reason for Design B: New parameters are easily added (no schema change) It is "Better Design" (throwing around the word normalization a lot) My reasons for Design A: 1:1 record to report (not 1:25 records) meta data overhead per row is 1/9 (opposed to 4/1) I'm not a database guru, so I can't fight back...but I feel I am hearing a line straight from a textbook. Critics of my design simply tells me it is wrong and keep repeating the mantra "It is not normalized". The way I see it the column itself describes the data, which means less data is stored... efficient and simple. I do see the appeal of Design A by effectively 'future-proofing' the database table. My application performing the inserts should not have to change, I just always insert the parameters I find in each report. I should add that I do expect future parameters to be added (maybe 5,10 more at most, but rather infrequently). Old and new versions will co-exist, but eventually all application instances should be updated. With Design A I would add new columns and set existing records to NULL. I would expect 100,000 reports a month or so. When I select from parameters I generally will show all parameters, so my queries become "select * from tbl where appid=x and reportId=y" with 1 result set necessary (not iterating over 25 results). I realize design A may not be "best", but I would prefer an friendly answer and not a canned response like I get from co-workers. Thanks in advance, Rich _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
