>>“… 740 tests” So, is there any way to explain/argue/enlighten/justify a "more righter" data model, as I think Jim (Bentley) is implying.
For example, wouldn't it be better to have a table named, say AUTOPSY_TEST_RESULTS, defined along the lines of: DecedentID (CaseID, or whatever - `PatientID` seems too optimistic ...) ... TissueTypeCode -- This allows for multiple tests of same tissue types. TestTypeCode TestResultValue ... Obviously, the "width" of a table like this would be vastly "thinner" than the one you describe, but the "depth" of the data is huge, i.e. the number of rows, which typically has a MUCH HIGHER limit than the number of columns. Of course, "that's the way we've always done it 'round here" could come into play, especially if they're using a "spreadsheet" (Data Browser in Edit mode?) approach to data entry. If they're actually using a form, it might be possible to make the underlying structure of a table like this (almost) invisible to your users. I will also offer a little example I thought of a couple of years back which relates to this situation. Use baseball. Baseball games normally consist of 9 innings (MLB, Adult, for any of you youth coaches/parents out there, but this example covers those, too.), with each side splitting an inning for "at bats" and "takin' the field". So, we design a table with 18 columns (9_inning * 2_sides_per_inning), right? Okay, what happens when we have extra innings? We can't really alter the structure of the table during a game. Even if we would, how many pairs of columns would we add? We can't know that "until the fat lady sings". And what of those kid games, some of which might last anywhere from 3-5 innings, assuming no tie and extra innings? Yes, we could use those 18 pairs of columns and say "fiddle dee dee" about the NULLs in the un-played innings, which we, being good db-types, don't like doing. Now, there is one simple but absolutely critical problem with this approach. How the heck do we automate the determination of the score? We cannot SUM() across columns, i.e. innings 1..N. However, if we have a table whose rows represent innings, we can handle baseball games of any length, SUM(scores), and everything else a good data model allows us to do by design. Now, this is all probably "old hat" to you. However, I love this example because most everybody in the USA knows at least a little something about baseball. Also, it doesn't really go into anything too technical, as it stays pretty much at a conceptual level, with baseball as a tangible example. As such, I think it's a useful explanation for why we should design a database in a way that might be different from what users have done, do, and will continue to do, until something like this happens. In fact, what happens if the government, at whatever level or branch, decide that there should be 741 tests, or 750, or 800 if the decedent is a suspected terrorist/animal_abuser/illegal_parker or whatever? The answer to "That'll never happen" is "How many tests were performed at autopsy N [Choose your number] years ago?" >> (nice to know when an autopsy is done there's about 740 different tests they >> have to do). Well, that depends on your point-of-view, I suppose ... |-[ or :-) Sorry to be so long-winded, but this sounds like a situation where you could use all the help you can find so that you can help your users. Steve in Memphis From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 4:41pm 16:41 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Database Limits, again? Steve: No, actually there shouldn't be any nulls in the table. When an autopsy is done they take all these tissue samples and have to report a value for each one to prove they did their job (nice to know when an autopsy is done there's about 740 different tests they have to do). This client has other tables with hundreds of columns, but not this big. They don't have many edit forms -- they are used to bringing the table up in the browser, inserting a row and then typing numbers all the way across. That's why they want it this way. BTW: I found the limit. I can get 583 columns to define, but adding a single extra causes the problems. Karen I’m gonna’ take a SWAG on one little thing – for now anyway – but I bet there a LOT of NULL column values in that table. I recall your using “client INSISTS”, but LOTS OF NULLS is a common indicator for poor data modeling. Steve in Memphis
