Design a database

Question

Hi, I want the best possible solution for the below stated problem. I am using SQL Server 2005 and asp.net 2.0.

I have a master table where all the test scripts of different projects are stored. From the test scripts in master table i need to create a module and add the required test scripts to the module.

UI design is: Create New Module. Select required test cases from the master table. Add the test cases to the module.

User can create as many modules as he wants with different combinations of test cases from master table.

For ex - Master table has 100 test cases. I create 2 modules. Module1 has 40 test cases selected from the master table. Module2 has 20 test cases selected from the master table.

How do i design a database in such a scenario?

Answer 1

Read Rules of Data Normalization

SUMMARY:

1. 1NF Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2. 2NF Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3. 3NF Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table. .......

Answer 2

To get to something like 3rd normal form, you basically need 3 tables at least.

You have the testcase table (which is your master table that holds all the test cases). Then you have a modules table, which creates an entry for each modules created by a user. Then you have a test_modules table, with a foreign key back to the modules table and the testcase table.

When you create a new module, you're going to insert a new row into the modules table. For each test case selected for that module, you'll insert a row into the test_modules table. Each row will have a FK to the modules table (so that you know what module it belongs to) and a FK to the testcase table (so you know what test case it is).

testcase Table (Master Table) {
    id
    test_case_name
}

modules table {
    id (PK)
    module_name (varchar)
    creater (varchar)
}

test_modules table {
    id (PK)
    testcase_id (FK to testcase.id)
    module_id (FK to modules.id)
}

So to get the test cases for a specific module, you can do a query like so:

select *
from modules m, testcase t, test_modules tmod
where m.id=tmod.module_id and tmod.testcase_id = t.id

This is a VERY oversimplified and inefficient query, but it will work to get you started.

Answer 3

"I have a master table"

"How do i design a database?"

The two previous answers have given you specifics concerning table design. Those specifics will be useful when you go to design the tables to store data pertaining to modules and scripts. However, you need to be aware of a couple of other fundamentals.

The design of your master table is part of database design. If the design of that master table is very unfortunate for the project you are taking on, then a redesign of the master table might be the best plan, depending on circumstances.

Normalization is not the only way to discover a good design. In particular, the rules quoted to you for 2NF and 3NF will help you to avoid anomalies when you go to do inserts, updates, and deletes on the tables you design. But noprmalization won't help you make selects any easier.

It takes a few weeks to learn how to follow the normalization rules. It takes much longer to learn when to disregard those rules, and how to come up with a good design when you do.