Query for data entry using SQL Server templates
Based on the answers to this question about dynamically accessing tables , I decided to take a couple of steps back and get some advice on a larger image.
I am revising a Windows Forms Application Database Project that I am rewriting for the Web using ASP.NET. Also, I ported our database to Sql Server so it can handle more traffic as the Access database is already overloaded. So, as a result of seeing what SQL Server can do, I am rethinking my database design decisions and their impact on my interface design.
The Windows interface currently displays a list of the latest codes:
02691 AFF1
32391 Lot# 23
etc.
For each code, there is an entry in the Productions table that starts with:
ProductionCode varchar(80),
Template varchar(50),
...
A template is one of several tables and also a foreign key in the template field definitions. All this information is used to dynamically create the DataGrid, starting in code.
There is a ScoreField table that represents all fields in all 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 contain the production code, the time for each test, and any data collected from the test.
So, to create my datagrid, on the fly, I start with
SELECT * FROM ProductionRun WHERE ProductionCode = @Code
In reality, I just get one result or abort the process if no results are received.
Where code is a line of code selected by the user (using a dropdown, not something that can be injected)
Then I do:
SELECT * FROM ScoreField WHERE Template = @Template
Where @Template is actually the Template field value for a single ProductionRun is returned.
Then I do:
SELECT * FROM @Template WHERE ProductionCode = @Code
But actually, I'm just linking the template name I got in the first part.
and then I use the result from ScoreField to add columns for each match result and set formatting and all.
But of course, as a result of this, at runtime, I cannot use data binding and have to programmatically populate all of my data.
So, when this database is revised, I look for a different, better approach. I have data in different tables and I want to apply formatting to the data and do it all in one interface, instead of forcing the user to guess what template their data is in. I want to be able to add templates without clogging up the system too much.
Obviously, this is not a simple programming question, but a best practice question, but I was looking for inspiration and / or examples to get me started on a different path.
source to share
What is the data that users are trying to see. Your best bet is to start by designing your database based on the data it holds, rather than how you might end up displaying it with the front-end.
If you are getting different data in different formats, then it is probably a bad idea to try to cast the whole thing to the same result type so that you can use data binding.
source to share
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 dimensions are historical, add a DATETIME column. Also, without knowing the specifics, the data types can obviously change.
Since each product type can have a different number of dimensions, you will likely end up creating your data grid on the fly using the format, adding columns as needed. I know you said you wanted to avoid this, but that is the price of making a generic app (I know you are not completely generic, but the more open or "free form" you try to make a claim, the more difficult it is to optimize and the more tradeoffs which you need to do).
One possibility might be to create a data grid with the maximum number of columns you want to support, and then have a stored procedure or view that returns that number of columns regardless of whether or not an actual dimension is available for the Product Type. Then you could bind to that SP or view and just hide columns that are not used at runtime.
source to share