Database Design for Filtering Database

Question

Hi All,

I'm new to complex database design. I'm currently into a project where the user should be able to retrieve Instructions based on a combination of 18 columns. So my parameter Table has the following columns

1. Job
2. State
3. Manager
4. ProcessCode
5. ProcessType (rest of the columns truncated).
6. InstructionID (FK of Instruction Table)

When adding / Modifying the instruction, he can choose multiple options in each of the above parameters. The Stored Procedure will store data in all combinations possible, in order facilitate easy retrieval, as during search (retrieval) only one option will be chosen in each of the columns.

There can be multiple instructions for same combination and the same instruction can apply to multiple combinations.

I have somehow created the SP for adding instruction, but am now struck with modification. When my Webpage passes the new combination to SP, what is the best way to update the Table?

I could delete all existing rows and create new rows for new combination, but I wanted to maintain the created date and created user columns. Further, there is a requirement to maintain history of these in a separate history table.

Sorry for the length of the question... And, Thank you for help.

Answer 1

What you have is many-to-many relationship, so I would suggest you use:

Answer 2

If you're trying to retrieve data based on a combination of parameters then you can set the parameters to have the default value of NULL e.g.

CREATE PROC spProcName
    @FieldName INT = NULL

The only other thing to do is set the WHERE section of the statement to look at the parameter values and compare them to see if they or null or not e.g.

WHERE ((FieldName = @FieldName) OR (@FieldName IS NULL))

Use this for querying the tables and use standard update queries in a similar fashion using the default parameter value of null but setting the value like this:

FieldName = ISNULL(@FieldName, FieldName)

Which lets you update only given parameters.

Hope this is something you are after, I can give a full example if needed.