3D or 4D table is SQL.

[khadem1386]

HiI have some 4D or 3D Dimations like ( DIM a(1 to 20, 1 to 3, 1 to 10) 20 X 3 X 10 in ASPand I want make a kind of 3D table in my Databases for transfer Data from Dim to TablesBut I think only we can make 2D table in all DATA BASES manegers (like SQL or Access).if yes, I have to make some addtionals fildes in 2D table instead 3D tabels?thak for your lead.

[nibe49]

HiYou can represent any number of dimensions in a table.Just have 1 column per dimension and 1 column for the valuesThe dimension columns will form a composite key, but it would be useful to add a unique primary keyNote that the Dim cols are int or long. The value can be any type.Rows can occur in any order in the table eg

<BR>Key	 Dim1	Dim2	Dim3	Value<BR>1		1		 1		1		 val_a<BR>2		1		 1		2		 val_b<BR>3		1		 2		1		 val_c<BR>4		2		 3		2		 val_d<BR>

regardsnibe

[nibe49]

Sorry about the formatting, I'm trying to represent a table with rows & columns & when I previewed it, it llooked ok.I hope you can make it out.regardsnibe

[rubyknight]

For a 2D table I would use One Table with 2 Primary Keys and One more additional table to store whatever valueFor a 3D table I would use One Table with 3 Primary Keys and One more additional table to store whatever valueFor a n-D table I would use One Table with n Primary Keys and One more additional table to store whatever valueLet's say, 3D table...create table THREE_DIMENSIONS ( X int not null, Y int not null, Z int not null, V varchar(32), --Or whatever variable type you wanna use/store constraint pk_three_dimensions primary key (X, Y, Z))If you take the X and/or Y and/or Z from different tables, you can add another foreign-key constraint, as follow: constraint fk_three_dimensions_x_table foreign key (X) references X_TABLE (X) on update cascade on delete cascade, constraint fk_three_dimensions_y_table foreign key (Y) references Y_TABLE (Y) on update cascade on delete cascade, constraint fk_three_dimensions_z_table foreign key (Z) references Z_TABLE (Z) on update cascade on delete cascadeAssume if you want to have a database relationship where deleting a record in X/Y/Z_TABLE's but do not want to erase the records that have same reference, the throw away the "on delete cascade".I'm not sure how many primary-keys you can have in an SQL database. Perhaps it's up to the architecture of different SQL software. If you are using Microsoft SQL, check inside the Books Online short-cut/tool.Hope this helps... or has nothing to do with what you asked.... oops...

[khadem1386]

thanks your attationscan you show me some sample.But why we don't have a real 3D or nD table is SQL?you konw that a Normal Table have 2 direct 2D (colums & Row )But my in formations have 3 or 4 Direct. 3D or 4DIn VB we can manage its. by Dim and OPEN RANDOM file.and save a dim with 4 layers(Direct) in a file.layer is such a sheet and cotain many cllsdo you understand me about 3D or 4D?I using ASP(VB) and Access SQL Now and want make a complex of 3d information.for example we have amont raining in days for a month(feb) this have 2 direct days & city (row and colum)and another table for March and another for April mont is third Direct of data andand we have 3 Feb in 3 years (2003, 2004, 2005) and now year is fourth direct (4D)I have many 4D or more DATA that sport by DIM in VB like DIM AAA(1 to 30, 1 to 12, 1 to 3)Thank for your Help

[nibe49]

Hello againPlease read my solution again and that of rubynight as they are essentially the same.Let's try and picture the table you want - with values. I'll try to do it without any formattingTable RAINFields ( Year, Month, City, Amount )Shows rainfall by year and month and city.Year- Month--- City- Amount2007 January- citya 1.12007 February citya 1.62007 February cityb 0.52007 January--cityb 0.42006 January--citya 1.2Fields Year, Month, City form a composite primary key.You can add another dimension (eg day of month) by adding another column, and then that would also have to be part of the key.Does this clarify things?regardsnibe