How to implement a "pointer" to an "array" ?

[Guest]

I am a novice wrt database design and desperately need help.

I have a table containing all things of type X, and another table containing
things of type R. Each R needs to refer to a subset of X, and there are many
Rs and many Xs. For example R1 needs to be associated with X3, X67, X92 and
X99, R5 needs to be associated with X13 and X67, R12 with X2, X3 and X4, and
so on.

My first thought (using generic C language terminology) is to implement this
as an attrribute of R which is a pointer to an array of pointers to specific
Xs. In otehr words, to somehow give R a field called say Xpointer. This
way, using my previous example, For R5 I could give Xpointer a value of
"Xarray5" and have "Xarray5" be a 1 column table (i.e. array)with fields name
Ptr containing values 13 and 67. Then I could create a query that says, in
effect, SELECT * FROM [X] WHERE [Xarray5]![Ptr] = [X]![ID]. I can't figure
out how to do this so that, for many Rs, I can create a single query with a
variable for the pointer array table name, something like "SELECT * FROM [X]
WHERE " & [R]![Xpointer] & " ![Ptr] = [X]![ID]" and have that concatenated
string somehow "be" an SQL statement.

Is there a way to do this? OR am I doing this all wrong? I realized
yesterday there are other ways to do it, such as added lots of columns to
table [X], one for each R, and simply entering a 1 for each X that applies to
that R, but that seems even less correct.

I'd also like to use this same construct, whatever it is, to create 1-field
tables to hold enumerated values, so that someone can lookup the name of an
enumeration and have the list of values returned.

Please help set me straight. I am hoping there is a simple way I can do
this without having to use VB, since I don't even understand where the VB
module would go, how to refer to it, how to do the declarations, or any of
that.

I hope this made sense, if not I can explain it more simply.

Thanks in advance!

Lee, San Diego

 

[George Nicholson]

The Relational Database solution (and that is what Access is...) would be to
create a table that represents the relationship (join) of data between
tableX and tableR. Otherwise I think you'll be reinventing the wheel.

TableRX

R1 X3
R1 X67
R1 X92
R1 X99
R5 X13
R5 X67
R12 X2
R12 X3
R12 X4

Then you can simply construct queries on the data to get the relevant result
set.

HTH,

 

[Guest]

I have used some join tables in my design, but since there are many Rs and
Xs, unless I am misunderstanding, I would either need to have many many rows,
e.g.

R1 X1
R1 X2
R2 X1
R2 X2
R3 X2
R3 X7
..
..
..
R999 X86
R999 X87 ...

or many many columns:

X1 R1 R2 R3 ... R999
a 1 0 1 0
b 0 1 0 1 ...

etc.

Is one of these the approach I should take?

The Relational Database solution (and that is what Access is...) would be to
create a table that represents the relationship (join) of data between
tableX and tableR. Otherwise I think you'll be reinventing the wheel.

TableRX

R1 X3
R1 X67
R1 X92
R1 X99
R5 X13
R5 X67
R12 X2
R12 X3
R12 X4

Then you can simply construct queries on the data to get the relevant result
set.

HTH,

I am a novice wrt database design and desperately need help.

I have a table containing all things of type X, and another table
containing
things of type R. Each R needs to refer to a subset of X, and there are
many
Rs and many Xs. For example R1 needs to be associated with X3, X67, X92
and
X99, R5 needs to be associated with X13 and X67, R12 with X2, X3 and X4,
and
so on.

My first thought (using generic C language terminology) is to implement
this
as an attrribute of R which is a pointer to an array of pointers to
specific
Xs. In otehr words, to somehow give R a field called say Xpointer. This
way, using my previous example, For R5 I could give Xpointer a value of
"Xarray5" and have "Xarray5" be a 1 column table (i.e. array)with fields
name
Ptr containing values 13 and 67. Then I could create a query that says,
in
effect, SELECT * FROM [X] WHERE [Xarray5]![Ptr] = [X]![ID]. I can't
figure
out how to do this so that, for many Rs, I can create a single query with
a
variable for the pointer array table name, something like "SELECT * FROM
[X]
WHERE " & [R]![Xpointer] & " ![Ptr] = [X]![ID]" and have that
concatenated
string somehow "be" an SQL statement.

Is there a way to do this? OR am I doing this all wrong? I realized
yesterday there are other ways to do it, such as added lots of columns to
table [X], one for each R, and simply entering a 1 for each X that applies
to
that R, but that seems even less correct.

I'd also like to use this same construct, whatever it is, to create
1-field
tables to hold enumerated values, so that someone can lookup the name of
an
enumeration and have the list of values returned.

Please help set me straight. I am hoping there is a simple way I can do
this without having to use VB, since I don't even understand where the VB
module would go, how to refer to it, how to do the declarations, or any of
that.

I hope this made sense, if not I can explain it more simply.

Thanks in advance!

Lee, San Diego

 

[George Nicholson]

Rows.

err, Records.

1) its proper data structure design
2) Access has a 255 field (not column) limit in both tables and queries, so
I suspect that option isn't really an option, even if it were desireable.

If R1 is related to 5 different X entities, then the join table will have 5
records describing the R1 relationships.
You can create a Unique index on the combination of the 2 fields to make
sure you don't have any duplicate information (you don't accidently want 2
records that both say R1/X1)

HTH,

I have used some join tables in my design, but since there are many Rs and
Xs, unless I am misunderstanding, I would either need to have many many
rows,
e.g.

R1 X1
R1 X2
R2 X1
R2 X2
R3 X2
R3 X7
.
.
.
R999 X86
R999 X87 ...

or many many columns:

X1 R1 R2 R3 ... R999
a 1 0 1 0
b 0 1 0 1 ...

etc.

Is one of these the approach I should take?

The Relational Database solution (and that is what Access is...) would be
to
create a table that represents the relationship (join) of data between
tableX and tableR. Otherwise I think you'll be reinventing the wheel.

TableRX

R1 X3
R1 X67
R1 X92
R1 X99
R5 X13
R5 X67
R12 X2
R12 X3
R12 X4

Then you can simply construct queries on the data to get the relevant
result
set.

HTH,


message

I am a novice wrt database design and desperately need help.

I have a table containing all things of type X, and another table
containing
things of type R. Each R needs to refer to a subset of X, and there
are
many
Rs and many Xs. For example R1 needs to be associated with X3, X67,
X92
and
X99, R5 needs to be associated with X13 and X67, R12 with X2, X3 and
X4,
and
so on.

My first thought (using generic C language terminology) is to implement
this
as an attrribute of R which is a pointer to an array of pointers to
specific
Xs. In otehr words, to somehow give R a field called say Xpointer.
This
way, using my previous example, For R5 I could give Xpointer a value of
"Xarray5" and have "Xarray5" be a 1 column table (i.e. array)with
fields
name
Ptr containing values 13 and 67. Then I could create a query that
says,
in
effect, SELECT * FROM [X] WHERE [Xarray5]![Ptr] = [X]![ID]. I can't
figure
out how to do this so that, for many Rs, I can create a single query
with
a
variable for the pointer array table name, something like "SELECT *
FROM
[X]
WHERE " & [R]![Xpointer] & " ![Ptr] = [X]![ID]" and have that
concatenated
string somehow "be" an SQL statement.

Is there a way to do this? OR am I doing this all wrong? I realized
yesterday there are other ways to do it, such as added lots of columns
to
table [X], one for each R, and simply entering a 1 for each X that
applies
to
that R, but that seems even less correct.

I'd also like to use this same construct, whatever it is, to create
1-field
tables to hold enumerated values, so that someone can lookup the name
of
an
enumeration and have the list of values returned.

Please help set me straight. I am hoping there is a simple way I can
do
this without having to use VB, since I don't even understand where the
VB
module would go, how to refer to it, how to do the declarations, or any
of
that.

I hope this made sense, if not I can explain it more simply.

Thanks in advance!

Lee, San Diego

 

[John Spencer]

Almost always many rows is the correct decision in a relational
database. For one thing the max number of columns is 255.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have used some join tables in my design, but since there are many Rs and
Xs, unless I am misunderstanding, I would either need to have many many rows,
e.g.

R1 X1
R1 X2
R2 X1
R2 X2
R3 X2
R3 X7
.
.
.
R999 X86
R999 X87 ...

or many many columns:

X1 R1 R2 R3 ... R999
a 1 0 1 0
b 0 1 0 1 ...

etc.

Is one of these the approach I should take?

The Relational Database solution (and that is what Access is...) would be to
create a table that represents the relationship (join) of data between
tableX and tableR. Otherwise I think you'll be reinventing the wheel.

TableRX

R1 X3
R1 X67
R1 X92
R1 X99
R5 X13
R5 X67
R12 X2
R12 X3
R12 X4

Then you can simply construct queries on the data to get the relevant result
set.

HTH,

I am a novice wrt database design and desperately need help.

I have a table containing all things of type X, and another table
containing
things of type R. Each R needs to refer to a subset of X, and there are
many
Rs and many Xs. For example R1 needs to be associated with X3, X67, X92
and
X99, R5 needs to be associated with X13 and X67, R12 with X2, X3 and X4,
and
so on.

My first thought (using generic C language terminology) is to implement
this
as an attrribute of R which is a pointer to an array of pointers to
specific
Xs. In otehr words, to somehow give R a field called say Xpointer. This
way, using my previous example, For R5 I could give Xpointer a value of
"Xarray5" and have "Xarray5" be a 1 column table (i.e. array)with fields
name
Ptr containing values 13 and 67. Then I could create a query that says,
in
effect, SELECT * FROM [X] WHERE [Xarray5]![Ptr] = [X]![ID]. I can't
figure
out how to do this so that, for many Rs, I can create a single query with
a
variable for the pointer array table name, something like "SELECT * FROM
[X]
WHERE " & [R]![Xpointer] & " ![Ptr] = [X]![ID]" and have that
concatenated
string somehow "be" an SQL statement.

Is there a way to do this? OR am I doing this all wrong? I realized
yesterday there are other ways to do it, such as added lots of columns to
table [X], one for each R, and simply entering a 1 for each X that applies
to
that R, but that seems even less correct.

I'd also like to use this same construct, whatever it is, to create
1-field
tables to hold enumerated values, so that someone can lookup the name of
an
enumeration and have the list of values returned.

Please help set me straight. I am hoping there is a simple way I can do
this without having to use VB, since I don't even understand where the VB
module would go, how to refer to it, how to do the declarations, or any of
that.

I hope this made sense, if not I can explain it more simply.

Thanks in advance!

Lee, San Diego

 

[Guest]

Lee,

Many rows.

If you do the many columns approach, the database gods will become very
displeased with you.

Seriously, there are limits to the # of columns in a DB table, and they must
be predefined in advance.This places artificial and arbitrary limits on the #
of ways you can model relationships between the two types of items.
There are no limits to the #s of rows and they don't have to be predefined.
Therefore, if you have data like this:

RValue XValue
R1 X11
R2 X1
R2 X2
R2 X3
R2 X4
.... <skipping rows>
R2 X11
R2 X12
.... <skipping rows>
R2 X999
....etc

You can accomodate n-n relationships between the X and R elements
This type of table is called a link table for that reason.
In Relational database models, there is the concept of table keys, which are
unique values defined for a given table (which is used to model a real-workd
concept/object/idea).
Relationships between tables are usually represented by one table defining a
unique key value, and another table receiving a copy (a Foreignly-defined key
value, or "Foreign Key" for short).
This allows the simple representation of a one->many relationship between
the two tables.
In the case like yours where there could be multiple items in table R
related in multiple ways to multiple items in table X, you put another table
in the middle between the two tables, and that middle table receives (ONLY,
usually) the foreign keys from both tables (attributes describing the
particular relationship may also be appropriate to include in this table,
like R2->X11, but only if X11 is the green kind)
This "link table" then permits defining N# of R <-> N# of X item
relationships between the X and R item tables.
You are correct that link tables can get quite lengthy, but handling lots of
data rows is exactly what relational database models are DESIGNED FOR.

HTH.

I have used some join tables in my design, but since there are many Rs and
Xs, unless I am misunderstanding, I would either need to have many many rows,
e.g.

R1 X1
R1 X2
R2 X1
R2 X2
R3 X2
R3 X7
.
.
.
R999 X86
R999 X87 ...

or many many columns:

X1 R1 R2 R3 ... R999
a 1 0 1 0
b 0 1 0 1 ...

etc.

Is one of these the approach I should take?

The Relational Database solution (and that is what Access is...) would be to
create a table that represents the relationship (join) of data between
tableX and tableR. Otherwise I think you'll be reinventing the wheel.

TableRX

R1 X3
R1 X67
R1 X92
R1 X99
R5 X13
R5 X67
R12 X2
R12 X3
R12 X4

Then you can simply construct queries on the data to get the relevant result
set.

HTH,

I am a novice wrt database design and desperately need help.

I have a table containing all things of type X, and another table
containing
things of type R. Each R needs to refer to a subset of X, and there are
many
Rs and many Xs. For example R1 needs to be associated with X3, X67, X92
and
X99, R5 needs to be associated with X13 and X67, R12 with X2, X3 and X4,
and
so on.

My first thought (using generic C language terminology) is to implement
this
as an attrribute of R which is a pointer to an array of pointers to
specific
Xs. In otehr words, to somehow give R a field called say Xpointer. This
way, using my previous example, For R5 I could give Xpointer a value of
"Xarray5" and have "Xarray5" be a 1 column table (i.e. array)with fields
name
Ptr containing values 13 and 67. Then I could create a query that says,
in
effect, SELECT * FROM [X] WHERE [Xarray5]![Ptr] = [X]![ID]. I can't
figure
out how to do this so that, for many Rs, I can create a single query with
a
variable for the pointer array table name, something like "SELECT * FROM
[X]
WHERE " & [R]![Xpointer] & " ![Ptr] = [X]![ID]" and have that
concatenated
string somehow "be" an SQL statement.

Is there a way to do this? OR am I doing this all wrong? I realized
yesterday there are other ways to do it, such as added lots of columns to
table [X], one for each R, and simply entering a 1 for each X that applies
to
that R, but that seems even less correct.

I'd also like to use this same construct, whatever it is, to create
1-field
tables to hold enumerated values, so that someone can lookup the name of
an
enumeration and have the list of values returned.

Please help set me straight. I am hoping there is a simple way I can do
this without having to use VB, since I don't even understand where the VB
module would go, how to refer to it, how to do the declarations, or any of
that.

I hope this made sense, if not I can explain it more simply.

Thanks in advance!

Lee, San Diego

 

[Guest]

Thanks George, John, and Mark. I truly appreciate the help.

Lee

Lee,

Many rows.

If you do the many columns approach, the database gods will become very
displeased with you.

Seriously, there are limits to the # of columns in a DB table, and they must
be predefined in advance.This places artificial and arbitrary limits on the #
of ways you can model relationships between the two types of items.
There are no limits to the #s of rows and they don't have to be predefined.
Therefore, if you have data like this:

RValue XValue
R1 X11
R2 X1
R2 X2
R2 X3
R2 X4
... <skipping rows>
R2 X11
R2 X12
... <skipping rows>
R2 X999
...etc

You can accomodate n-n relationships between the X and R elements
This type of table is called a link table for that reason.
In Relational database models, there is the concept of table keys, which are
unique values defined for a given table (which is used to model a real-workd
concept/object/idea).
Relationships between tables are usually represented by one table defining a
unique key value, and another table receiving a copy (a Foreignly-defined key
value, or "Foreign Key" for short).
This allows the simple representation of a one->many relationship between
the two tables.
In the case like yours where there could be multiple items in table R
related in multiple ways to multiple items in table X, you put another table
in the middle between the two tables, and that middle table receives (ONLY,
usually) the foreign keys from both tables (attributes describing the
particular relationship may also be appropriate to include in this table,
like R2->X11, but only if X11 is the green kind)
This "link table" then permits defining N# of R <-> N# of X item
relationships between the X and R item tables.
You are correct that link tables can get quite lengthy, but handling lots of
data rows is exactly what relational database models are DESIGNED FOR.

HTH.

I have used some join tables in my design, but since there are many Rs and
Xs, unless I am misunderstanding, I would either need to have many many rows,
e.g.

R1 X1
R1 X2
R2 X1
R2 X2
R3 X2
R3 X7
.
.
.
R999 X86
R999 X87 ...

or many many columns:

X1 R1 R2 R3 ... R999
a 1 0 1 0
b 0 1 0 1 ...

etc.

Is one of these the approach I should take?

The Relational Database solution (and that is what Access is...) would be to
create a table that represents the relationship (join) of data between
tableX and tableR. Otherwise I think you'll be reinventing the wheel.

TableRX

R1 X3
R1 X67
R1 X92
R1 X99
R5 X13
R5 X67
R12 X2
R12 X3
R12 X4

Then you can simply construct queries on the data to get the relevant result
set.

HTH,


I am a novice wrt database design and desperately need help.

I have a table containing all things of type X, and another table
containing
things of type R. Each R needs to refer to a subset of X, and there are
many
Rs and many Xs. For example R1 needs to be associated with X3, X67, X92
and
X99, R5 needs to be associated with X13 and X67, R12 with X2, X3 and X4,
and
so on.

My first thought (using generic C language terminology) is to implement
this
as an attrribute of R which is a pointer to an array of pointers to
specific
Xs. In otehr words, to somehow give R a field called say Xpointer. This
way, using my previous example, For R5 I could give Xpointer a value of
"Xarray5" and have "Xarray5" be a 1 column table (i.e. array)with fields
name
Ptr containing values 13 and 67. Then I could create a query that says,
in
effect, SELECT * FROM [X] WHERE [Xarray5]![Ptr] = [X]![ID]. I can't
figure
out how to do this so that, for many Rs, I can create a single query with
a
variable for the pointer array table name, something like "SELECT * FROM
[X]
WHERE " & [R]![Xpointer] & " ![Ptr] = [X]![ID]" and have that
concatenated
string somehow "be" an SQL statement.

Is there a way to do this? OR am I doing this all wrong? I realized
yesterday there are other ways to do it, such as added lots of columns to
table [X], one for each R, and simply entering a 1 for each X that applies
to
that R, but that seems even less correct.

I'd also like to use this same construct, whatever it is, to create
1-field
tables to hold enumerated values, so that someone can lookup the name of
an
enumeration and have the list of values returned.

Please help set me straight. I am hoping there is a simple way I can do
this without having to use VB, since I don't even understand where the VB
module would go, how to refer to it, how to do the declarations, or any of
that.

I hope this made sense, if not I can explain it more simply.

Thanks in advance!

Lee, San Diego