473,320 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Store data horizontally or vertically?

My project is to automate testing of Stored Procedures
of type SELECT (at least for now).

I want to create a table where each stored procedure's
input parameter values are entered and in another table
the expected result value(s) are entered when executed
against a sample database containing manually entered
and verified data.

My current problem is that the stored procedures' input
parameters range from none to 50 parameters (ok now
that I think of it maybe this SP with 50 parameters is an
INSERT SP; regardless let's assume I have SPs that
require 10-20-30+ parameters).

My other problem is each stored procedures' returned
result could fall into any of these four categories:
- 1 row, 1 column
- 1 row, many columns
- many rows, 1 column
- many rows, many columns

So far I thought about 3 ways of storing the data:
1- 1 large table with 50 columns that can hold various
number of input parameter values. Similar type of table for
holding the output result for the four categories above.
2- 1 small table holding one value per row. All kinds of joins
with other tables to indicate which SP and which column the
value belongs to...
3- 1 individual table per stored procedure, this way the number
of columns in the table would match exactly the number of input
parameters.

Obviously the above 3 categories could apply both for the input
and output data.

Now I'm still in research mode and I haven't decided on any choice
yet. And I know each approach has serious consequences; let's just
say they are all with some limitations.

Has anyone dealt with this scenario before? This is the first time I
need a table to hold various types of returned data.

Just to give some numbers: possibly a few thousand SPs and very
likely each SP would have more than one test scenario.

What would you suggest?

Thank you
Nov 11 '05 #1
2 4201
On Thu, 10 Nov 2005 23:33:16 -0500, "serge" <se****@nospam.ehmail.com> wrote:
My project is to automate testing of Stored Procedures
of type SELECT (at least for now).

I want to create a table where each stored procedure's
input parameter values are entered and in another table
the expected result value(s) are entered when executed
against a sample database containing manually entered
and verified data.

My current problem is that the stored procedures' input
parameters range from none to 50 parameters (ok now
that I think of it maybe this SP with 50 parameters is an
INSERT SP; regardless let's assume I have SPs that
require 10-20-30+ parameters).

My other problem is each stored procedures' returned
result could fall into any of these four categories:
- 1 row, 1 column
- 1 row, many columns
- many rows, 1 column
- many rows, many columns

So far I thought about 3 ways of storing the data:
1- 1 large table with 50 columns that can hold various
number of input parameter values. Similar type of table for
holding the output result for the four categories above.
2- 1 small table holding one value per row. All kinds of joins
with other tables to indicate which SP and which column the
value belongs to...
3- 1 individual table per stored procedure, this way the number
of columns in the table would match exactly the number of input
parameters.

Obviously the above 3 categories could apply both for the input
and output data.

Now I'm still in research mode and I haven't decided on any choice
yet. And I know each approach has serious consequences; let's just
say they are all with some limitations.

Has anyone dealt with this scenario before? This is the first time I
need a table to hold various types of returned data.

Just to give some numbers: possibly a few thousand SPs and very
likely each SP would have more than one test scenario.

What would you suggest?

Thank you


Clearly, if you're going to use relational db tables to store the data, the
benefits of 1-m relationships outweigh the benefits of n-columns. That said,
when you're a hammer, every problem looks like a nail, and I'm not sure that
database tables are the best medium for storing your tests or results.

Check out fit.c2.com. Notice that there is a .NET implementation of FIT, that
should work very nicely as a platform to create FIT tests for MS SQL Server
stored procedures, though JDBC from Java would probably work fine, too.
Nov 11 '05 #2
serge (se****@nospam.ehmail.com) writes:
My other problem is each stored procedures' returned
result could fall into any of these four categories:
- 1 row, 1 column
- 1 row, many columns
- many rows, 1 column
- many rows, many columns

So far I thought about 3 ways of storing the data:
1- 1 large table with 50 columns that can hold various
number of input parameter values. Similar type of table for
holding the output result for the four categories above.
2- 1 small table holding one value per row. All kinds of joins
with other tables to indicate which SP and which column the
value belongs to...
3- 1 individual table per stored procedure, this way the number
of columns in the table would match exactly the number of input
parameters.

Obviously the above 3 categories could apply both for the input
and output data.

Just to give some numbers: possibly a few thousand SPs and very
likely each SP would have more than one test scenario.


At the one extreme, one does a full-fledged data model with a table
store_procedures that defines the procedure as such. Then there is a
sub-table that defines the output columns, and then there is one that
holds with rows with output data. Note here that there would be one row for
each value. That is, if an SP returns 20 columns, one row in the result
set results in 20 rows in this table. It will be akweard to do this
transposing. For the input data you would have a similar arrangement,
although somewhat simpler, as parameters do not come in rows.

The other extreme is to say that each procedure is an object of its own,
and thus needs a table of its own. Here output rows can be stored as
rows. But with the number of tables you indicate, this will be quite
unmanagable. Then again, while the first extreme has fewer tables, there
is still the same amount of data to manage.

What I don't like is some sort of a middle ground as you indicate in
your first alternative. Somewhere you will need to store that colunm 25
for procedure A has the data type datetime. Then again, this is probably
the model that makes the test engine easiest to implement.

Steve indicated in his reply that a relational database may not be the
best data store for this situation. And maybe he is right. One solutinn
that has some appeal to me is to use XML. Each XML document would then be
the output from a stored procedure. Parameters could be handled in the same
way, but regular table storage may still work for them, as they have one
dimension less.

In SQL 2005 there is a native xml data type which simplifies this
implementation, but you coudl use ntext in SQL 2000 for the XML
docuemnts. You would have to get all data to client level for comparisons
though.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: mittal.pradeep | last post by:
What is the better table design for a data collection application. 1. Vertical model (pk, attributeName, AttributeValue) 2. Custom columns (pk, custom1, custom2, custom3...custom50) Since the...
6
by: Larry R Harrison Jr | last post by:
I have Access 97, and I have a report with a text box which has the "Can Grow" set to yes. Regardless, it still doesn't show all the data of the tet box. It does grow vertically, but the data...
1
by: Ravikanth[MVP] | last post by:
Hi Mike, One suggestion, include datagrid in div with scroll bars. Ravikanth >-----Original Message----- >I have loads of data to scroll through in my datagrid and therefore need
1
by: Craig Banks | last post by:
If a row of data in a dataset has a lot of columns the row displaying the data in a datagrid will run way off the screen. What I'd like to do is display a row of data over several datagrid rows so...
2
by: sachjn | last post by:
Hi, I need to have a table along with other UI items in page which resizes horizontally and vertically with the browser window. That means table should accoupy the remaining available space in...
1
by: AimeeRae | last post by:
I need to create a report based off the following table (column headings): Station/Date/TransType/Offer1/Offer2 Station can be either: (KTTB or WOA) Date is variable TransType can be either:...
1
by: tanya2001 | last post by:
hi all now i have a query on crystal reports in .net.I have to generate a report in my webform by displaying one column vertically and other one horizontally....so how do u suggest...i should use...
2
by: HowHow | last post by:
Using Access 2000, normally query returns result horizontally. Is there any way to set the query to return result vertically? For example, my query shown the result as below: Heading ...
2
by: ghjk | last post by:
I'm developing web based system using php and mysql. In there I want to drag and drop information horizontally and vertically. How can I do that? It is a table like below. ex: monday ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.