472,792 Members | 4,628 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Proper table structure and SQL Server limits

Hello all,

I am migrating a Paradox application to SQL Server. My problem is that the
existing Paradox table structure is limited. Correcting it will mean a
re-write of the application (Delphi app). Although the record and column
limits are higher in SQL Server, it will eventually hit the wall. So I was
wondering if anyone could offer advise on how we might do this best....

The application is essentially an evaluation tool. It is very generic and
can evaluate anything from buying a digital camera or laptop, to purchasing
a house. All the attributes of whatever it is being measure (the
attributes) are defined as a new column.

Example: let's say you are evaluating laptops. The core table may look like

1) Identifier
2) Processor Speed
3) Processor Cache
4) Hard Disk Size
5) LAN connection
6) Cost

So in this table, there are 5 things being measure, 6 columns in total. A
record might look like this:

Field Data:
Identifier | Speed | Cache | HD Size | LAN | Cost ...
HP | 3ghz | 2mb | 60G | Wireless | $2000
So you can see that this structure will hit the wall eventually as more and
more criteria are added. With Paradox, the max number of columns is 255. In
SQL Server this is 1024. Of course there is also a maximum record size. For
SQL Server this is 8060 bytes.

What I'd prefer is a structure that has no limits on how big it can be. In
short, I need a new table structure that is long and skinny, vs very wide
(or fat):


So using the above example, the data might look like:

Field Data
Identifier | Attribute | Value
HP | Speed | 3ghz
HP | Cache | 2mb
HP | HD Size | 60G
HP | LAN | Wireless
HP | Cost | $2000

Obviously this structure is preferred because now I won't hit any column or
record limits, but it makes just about everything else in the application
more difficult. Fundamentally data validation becomes difficult

In the above example, how would one define this structure?
Identifier varchar(50)
Attribute varchar(50)
Value ???? <-- here lies my problem!!!

In some cases "value" would be an integer, sometimes numeric, sometimes
text. So then I figured, well I could have a number of tables as above,
each for the various data types, integers, numbers, free form text, etc.

For Integer data
Identifier varchar(50)
Attribute varchar(50)
Value integer

For floating point data
Identifier varchar(50)
Attribute varchar(50)
Value numeric

For free-form data
Identifier varchar(50)
Attribute varchar(50)
Value varchar or ntext
So now I have taken what was in one table, and split it across 3 or more
tables. Of course, all this data would need to be "re-connected" in a grid
like fashion so it looks like just one row of data to the client again.

It all seems to be getting very messy - to simply overcome a column/row
size limitation. Surely there must be an eloquent way to do something like

thanx very much for any input.

-randall sell
Jul 20 '05 #1
0 2399

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

Similar topics

by: Anand | last post by:
Hi all Please help me to find out table size in MS-SQL how can I count or identify, this specific table is using some xyz kb of space of my hdd. thanks
by: Ben | last post by:
Hi everyone, Now I am using Visual Basic.net to write a program but having some headaches. The situation is as follows: I am using ODBC connection to connect one database( non-SQL server) ,...
by: Peter Kirk | last post by:
Hi there can someone please help me with creating dynamic content in a table? For example, see the below javascript and html - why is a new row not created in the table when I click the button?...
by: MB | last post by:
Hello everyone! These lines were inspired by the "DIV without line breaks" posted by Johnny Two Dogs on June 22. I know it is an old issue, but I want to share a few remarks that could be of...
by: Garris, Nicole | last post by:
We've actually implemented this kind of thing in a different DBMS. The physical design consists of a single "organization" table that's something like: Org_key (primary key) Org_type (group,...
by: Xah Lee | last post by:
if i want to have a empty link, which way is more proper? <a href=""> <a href="#"> <a href="javascript:void(0);"> Xah xah@xahlee.org ∑ http://xahlee.org/
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database...
by: jc | last post by:
Hello. I want to ask about the possibility of copying both a table structure and it's contents from a SQL server table to a table within MS access. The problem cannot be solve with a permanent...
by: Memborg | last post by:
Hej Everybody I have tried do make an "instant search" in javascript, ie everytime one hits a key the javascript sends the request to a database and returns the result into a table. The...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?

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.