May 8, 2009 at 7:46 am
I'm using SQL to store data where one of the fields is a large array. I was going to put this array into another table that is seperate from the "main" table of data. Is it possible to have an entry in "main" at the location of the array amongst all the other dara that points to the "array" table?
Thanks!
May 8, 2009 at 8:09 am
It would be great if you put any example for better understanding 🙂
Abhijit - http://abhijitmore.wordpress.com
May 8, 2009 at 8:40 am
Sorry didn't mean to be vague!
Example:
You have a list of data:
a = 1
b = 2
c = 3
d = [1:1:100] (an array of values 1-100 - 1, 2, 3 ... 100)
Since SQL doesn't store an array in a cell, my plan is to have a table, "Main" that has columns, a, b, c, and d. Row 1 of a, b, and c will hold the values 1, 2, and 3, but column d can't hold the array [1:1:100]. So, instead I intend to have another table, labeled "d" that has the array in it, with the column names being the index of the array. (column "1" is array value at space "1")
So:
can I have column 'd' row '1' in "Main" be a pointer to Table "d" so that if I query
SELECT d FROM dbo.Main
I get
SELECT * FROM dbo.d
Thanks! Let me know if my explanation is poor 😛
May 8, 2009 at 8:52 am
Sounds like a one to many relationship.
Use the primary key of the main table that links to X number of rows in the second table. So for your array sitation:
MainTable
MainTableId (PK)
Val1
Val2...
ArrayTable
ArrayTableID (PK)
MainTableId (FK)
ArrayValue
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2009 at 9:38 am
veritas_86 (5/8/2009)
I'm using SQL to store data where one of the fields is a large array. I was going to put this array into another table that is seperate from the "main" table of data. Is it possible to have an entry in "main" at the location of the array amongst all the other dara that points to the "array" table?
I think that Grant ahs already covered your specific situation very well, but I wanted to add a note about the general concept of one thing "pointing" to another in SQL Server.
I have always found that one of the most important pragmatic concepts about relational databases for developers to grasp is that "pointing" in a relational environment is reversed or backwards from the way that it typically would be done a general purpose programming language.
In a GP language, a parent-child structure with one or more children works by the parent object having (lists of) pointers to the child objects:
Parent
|
+---->Child1
+---->Child2
+---->Child3
Then to find all of the related objects, you first find the parent and then traverse the pointers/references to find each of the child objects.
However, in a relational design, it is the children that point to the parent. This is accomplished by having each row in a "child" table have either a "ParentID" column or ParentKey(s) column(s) that incorporates the parent's primary key (or ID column as a surrogate) into the child row (frequently as part of its own primary key).
Then to find all of the child rows, you first find the parent row in the parent table and then use the Primary Key (or ID) to find the child rows associated with that particular parent row (if you already know the ParentID or PK, then you can skip getting the Parent row first):
SELECT ChildTable.*
From ChildTable
Where ChildTable.ParentID = @ParentID
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 10:23 am
Sounds good, thanks everyone!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply