---

Newbee - Please bare...

I need to establish a database. I know what I want, but not how to get
there.

I need a database to hold a number of parameters that I will access from
another program. The other program is a GIS (Geographical Information
System), but I don't think that is very important for my question.

I have a visual idea about the database that I need.
Considering a ordinary table as a two dimensional (2D) data space, with
the two dimensions represented by rows and cols, respectively. I need a
4D (may be 5D or 6D) data space. I should be able to hold a small number
of information for each of a limited number of situations in a forest
environment. The situation in the forest is defined by four parameters
each representing the four dimensions. A "soil type" (dimension 1), a
"tree species" (dimension 2), a "management type" (dimension 2) and a
"precipitation value" (dimension 2).

Getting information out of the database will (as I see it) require
looking up a location in a 4D data space. I know the soil type, tree
species, the management and precipitation. I need the data base to
return a number of values (less than ten floats) which are associated
with that given forest situation. I can think of this also as a function
returning a block of values e.g. F(soil, species, management,
precipitation)=Result. Where Result are a array or some other data
structure holding the relevant values.

The 4D data space are not even that big. We expect to be using 7
soiltypes, 5 tree species, 3 managements and 9 precipitation intervals.
This gives less than 1000 possible locations in the 4D data space.

The tricky part
There is a catch, of cause, why I don't implement this as a simple .CSV
file.
1) I need this to be accessible, in an easy way, from the GIS system.
Specifically, the programming language Avenue, from ESRI.
This in terms mean that a ODBC and SQL enabled access would be
preferable.
2) We need SPEED. To meet acceptable reply times for the system as such
we would like close to 10.000 records / second, out of the data base.

Q: Is this possible - How do I do - What data structure is optimal?

All comments and suggestions are welcomed...

Best Regards
Martin Hvidberg (mhv@fsl.dk)

PS. We have access to ms-access, a ms-SQL-server and a MySQL server.

---

Martin Hvidberg <Martin@Hvidberg.net> wrote in message news:<3C3C1B70.1C35E5A@Hvidberg.net>...
> Newbee - Please bare...
>
> I need to establish a database. I know what I want, but not how to get
> there.

No problem.

>
> I need a database to hold a number of parameters that I will access from
> another program. The other program is a GIS (Geographical Information
> System), but I don't think that is very important for my question.
>
> I have a visual idea about the database that I need.
> Considering a ordinary table as a two dimensional (2D) data space, with
> the two dimensions represented by rows and cols, respectively. I need a
> 4D (may be 5D or 6D) data space. I should be able to hold a small number
> of information for each of a limited number of situations in a forest
> environment. The situation in the forest is defined by four parameters
> each representing the four dimensions. A "soil type" (dimension 1), a
> "tree species" (dimension 2), a "management type" (dimension 2) and a
> "precipitation value" (dimension 2).
>
> Getting information out of the database will (as I see it) require
> looking up a location in a 4D data space. I know the soil type, tree
> species, the management and precipitation. I need the data base to
> return a number of values (less than ten floats) which are associated
> with that given forest situation. I can think of this also as a function
> returning a block of values e.g. F(soil, species, management,
> precipitation)=Result. Where Result are a array or some other data
> structure holding the relevant values.
>
> The 4D data space are not even that big. We expect to be using 7
> soiltypes, 5 tree species, 3 managements and 9 precipitation intervals.
> This gives less than 1000 possible locations in the 4D data space.
>
> The tricky part
> There is a catch, of cause, why I don't implement this as a simple .CSV
> file.
> 1) I need this to be accessible, in an easy way, from the GIS system.
> Specifically, the programming language Avenue, from ESRI.
> This in terms mean that a ODBC and SQL enabled access would be
> preferable.
> 2) We need SPEED. To meet acceptable reply times for the system as such
> we would like close to 10.000 records / second, out of the data base.
>
> Q: Is this possible - How do I do - What data structure is optimal?
>
> All comments and suggestions are welcomed...
>
> Best Regards
> Martin Hvidberg (mhv@fsl.dk)
>
> PS. We have access to ms-access, a ms-SQL-server and a MySQL server.

OK. Assuming the approximately ten floats are consistent over any
combination of forest situation parameters (i.e. you are interested in
the same qualitative set of data no matter what values the forest
situation values assume), and that you must also specify all four
forest situation parameters to identify a desired set of your float
data, you can easily set this up as a single table in SQL Server.

Despite what you posted above about a table in an RDB being a
two-dimensional data space, you can easily represent data with higher
dimension by using a composite primary key. I recommend studying a
good book on basic relational database principles; it will pay great
dividends as you represent more complicated data.

Here is the DDL for something similar to what you will need under SQL
Server. I did not know the types of your situation parameters, so I
guessed. On any kind of machine capable of running SQL Server, with
only around 1000 records in this table, you will find the speed you
can access this data to be greatly faster than your requirements, and
you will be able to perform all sorts of interesting analysis of your
data using pretty basic SQL. Hope this helps!

CREATE TABLE dbo.Forest_data
   (
   Soil_type int NOT NULL,
   Tree_species int NOT NULL,
   Management_type int NOT NULL,
   Precipitation_value int NOT NULL,
   Data_1 float(53) NULL,
   Data_2 float(53) NULL,
   Data_3 float(53) NULL,
   Data_4 float(53) NULL,
   Data_5 float(53) NULL,
   Data_6 float(53) NULL,
   Data_7 float(53) NULL,
   Data_8 float(53) NULL,
   Data_9 float(53) NULL,
   Data_10 float(53) NULL
   ) ON [PRIMARY]
GO
ALTER TABLE dbo.Forest_data ADD CONSTRAINT
   PK_Forest_data PRIMARY KEY NONCLUSTERED
   (
   Soil_type,
   Tree_species,
   Management_type,
   Precipitation_value
   ) ON [PRIMARY]
GO