January 7, 2004 at 6:34 am
Disclaimer: I apologize if I have a lack of understanding, as I only have book-learned experience in databasing, and no experience in SQL Server. 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:
id desc etc program1 program2 program3 ............ programX
001 blah blah yes no yes .......................
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:
id program_name
001 program1
001 program3
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!
January 7, 2004 at 12:10 pm
You're describing a many-to-many relationship. As you suggest, you want three tables: Subtest, Program, and SubtextXProgram. (Just an example, choose your own naming convention.)
The last table you describe would be the join table, SubTextXProgram, except that you want to store the ProgramIDs, not the descriptions.
You're right, this type of table is difficult to read. It isn't meant to be read, it's meant to store the data. To read the data, create a view which gives you the ids, descriptions and any other information you might want from the two parent tables.
January 7, 2004 at 12:22 pm
Thanks! I had asked around on a lot of forums, and found just what you said (that my suggested design change is the "correct" way to go), but no one had told me about using the view to see the data the way I would like to see it. That helps alot!!!!!
Thanks a ton!
-JD
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply