SQL Server Template-Driven Data Entry Question

Question

Based on the answers to this question about dynamically accessing tables, I've decided to take a couple steps back and get some advice on the larger picture.

I am revisiting the database design of a Windows Forms application which I am rewriting for the Web using ASP.NET. Also, I have ported our database to Sql Server, so it can handle more traffic, as the Access database is already getting overburdened. So, as a result of seeing what SQL Server can do, I have been revisiting my database design decisions, and their effect on my user interface design.

Currently, the windows interface displays a list of recent codes:

02691 AFF1
32391 Lot# 23

and so on.

For each code there is a record in a Productions table that starts with:

ProductionCode varchar(80),
Template       varchar(50),
...

The template represents one of a number of tables, and also a foreign key into template field definitions. All of this information is used to build the DataGrid dynamically, starting from a code.

There is the ScoreField table, which represents all the fields in all the templates, except ProductionCode (which is in all of them as a foreign key).

ScoreField

Template       varchar(50)
Field          varchar(50)
Formatting     varchar(50)  // This is a .NET style formatting string, say 0.00 or ##
...

Then there are the Template tables themselves, which hold a ProductionCode, a Time for each test, and whatever data was collected by the test.

So to create my datagrid, on the fly, I start by

SELECT * FROM ProductionRun WHERE ProductionCode = @Code

In reality, I just get the one result, or abort the process if no results are obtained.

Where the code is the code string selected by the user (using a drop down, not something vunerable to injection)

Then I do:

SELECT * FROM ScoreField WHERE Template = @Template

Where @Template is actually, the Template field value for the one record of ProductionRun returned.

Then I do:

SELECT * FROM @Template WHERE ProductionCode = @Code

But actually, I just concatenate the Template name I got in part one.

and then I use the result from ScoreField to add columns for each matching result and set up the formatting, et all.

But of course, as a result of doing this all at run time, I don't get to use databinding, and have to programmatically fill in all my data.

So, with this database revisit, I'm looking for another, better approach. I've got data in different tables, and I want to apply formatting to the data, and be able to do it all within one interface, instead of forcing the user to guess what template their data is in. I want to be able to add templates without screwing up the system too much.

Obviously, this is not a simple programming question, but more a question of best practices, but I was looking for some inspiration and/or examples to get me started down a different path.

Answer 1

What is the data that the users are trying to actually see. Your best bet is to start out by designing your database based on the data that it's holding and not based on how you might end up displaying it to a front-end somewhere.

If you are getting different data in different formats then it's probably a bad idea to try to force it all into a single type of result just so that you can use data binding.

Answer 2

Given your comment above, I would probably model it something like below.

CREATE TABLE dbo.Products (
    product_code VARCHAR(10) NOT NULL,
    product_name VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (product_code)
    )
GO
CREATE TABLE dbo.Measurement_Types (
    measurement_type_code   VARCHAR(10)  NOT NULL,
    measurement_type_description VARCHAR(255) NOT NULL,
    format        VARCHAR(20)  NOT NULL,
    CONSTRAINT PK_Measurement_Types PRIMARY KEY CLUSTERED (measurement_type_code)
    )
GO
CREATE TABLE dbo.Measurements (
    product_code   VARCHAR(10)  NOT NULL,
    measurement_type_code VARCHAR(10)  NOT NULL,
    measurement_value  DECIMAL(10, 4) NOT NULL,
    CONSTRAINT PK_Measurements PRIMARY KEY CLUSTERED (product_code, measurement_type_code),
    CONSTRAINT FK_Measurements_Products FOREIGN KEY (product_code) REFERENCES dbo.Products (product_code),
    CONSTRAINT FK_Measurements_Measurement_Types FOREIGN KEY (measurement_type_code) REFERENCES dbo.Measurement_Types (measurement_type_code)
    )
GO

If the measurements are historical then add a DATETIME column. Also, not knowing specifics the data types obviously might change.

Since each product type can have a varying number of measurements then you will probably end up building your data grid on the fly using the format, adding columns as necessary. I know that you said that you wanted to avoid that, but it's a price to pay for making a generic application (I know you're app isn't completely generic, but the more open or "freeform" you try to make an application, the harder it is to optimize and the more compromises that you need to make).

One possibility would also be to create a data grid with the maximum number of columns that you want to support and then have a stored procedure or view which returns that number of columns regardless of whether or not an actual measurement is available for a product type. You could then still bind to that SP or view and simply hide the columns that aren't used at run time.