Disclaimer: I apologize if I have a lack of understanding, as I only have book-learned experience in databasing. With that being said....
I am on a team to re-design a database. The database contains information about a test plan (testing hardware and software). There is one feature of the current database that I find rather undesirable as far as database design is concerned, and I want to change it. Problem is, I don't know the best way to do so.
There is currently a table (subtest) which has standard fields such as the subtest description, and subtest id, but it also has fields for the project that the subtest is applicable to. This ends up looking something like so:
These fields right now keep getting added on with each new incoming program. Eventually, I see this getting out of hand. Additionally, as it is now, whenever a new program field is introduced, someone must manually go in and check off which subtests are "applicable" to that new program.
I assume it would make more sense (database design wise) to take those program fields out, consider the "program" attribute of the "subtest" entity to be a multivariable attribute, and make a single table containing subtest id, and the applicable programs....like so:
But with around 5000 subtests, this seems like the above table could get very long, and very difficult to read.
Does anyone have any thoughts about how this might be best implemented? Any help would be GREATLY appreciated!
I may have confused you. There would not be 5000 columns. There would be 5000 subtests i.e:
Rather, I was saying that there ends up being these "applicable" fields in the table, which grow more and more (right now there is around 9, but that will only grow larger and larger). These fields are shown in my first post by "program1" "program2" etc.
The problem I see with my solution is that the multivariable attribute table "Programs" would get insanely long. For example, if half of the subtests were applicable to all 9 programs, you would have something like so:
ProgramID and SubtestID are surrogate keys.
SubtestName is the name I gave for your numeric identifer for subtest
IsPassed is the name I gave for your yes/no values.
You can't escape having 5000 rows somewhere, but you can normalize and have one unique list of Programs, one unique list of Subtests and match Program to Subtest in ProgramSubtest. Your SQL will be much cleaner this way and after populating it the first time with existing data all later additions, modifications and deletions will be much smoother.
First off, thanks to everyone for the quick replys!
It sounds like from r937's that I am right about the current design being undesirable, and my suggested change might be better conceptually as well as far as querys are concerned.
I'm going to add in a twist here that I didn't add in before (for simplicity sake).
Currently, two of the "applicable" fields refer to programs. The other 7 refer to sections. Now, the other fundemental problem I am seeing is that the sections listed in these columns are ALSO table values in the table "sections". To show you an example:
Thanks for the info. That might work, although it is a bit off from what I had in mind.
Does anyone else have any thoughts?
My last little gotcha to add into this mess is that we would like to add yet another applicability entity that handles platforms. To make things a little clearer, hear is a look at how the hierarchy looks:
*Multiple Programs (program1, program2) These are added quarterly.
*Within each program, there are mutiple sections of testing (section1, section2, section3)
*Below sections are the Tests (I haven't discussed the tests table because I feel that I have that part worked out.....basically ignore this for now).
*Below the test table, we have subtests (the heart of everything). When a new program starts getting tested, currently a new field is added to the subtest table for that program. As previously mentioned, there are also fields for sections, but these sections rarely (if ever) change, so we can pretty much forget about worrying about new sections being added.
*A subtest can be tested against any number of platforms that are introduced in a program. We don't have anything implemented for this yet. I think this should somehow be similar to what we discussed above as far as applicability with programs. (such as another table containing subtest_id & platform_name)
I hope I'm not losing everyone along the way. I was trying to clarify, but I probably made things more confusing then ever.
I'll say it again, I REALLY appreciate all the help I am getting, as I want to design this database well. Help from the experts is what I need most!
Platforms are various model numbers that software is designed to run on. Each project has new model numbers (new hardware) and new software that runs on each of those. The software and hardware are slightly different accross platforms. Because of this, a subtest might be applicable to one platform but not to another.