Special table relation - does it have name?

[benzhu]

I have a need to create these two tables:

Scheme 1:
Table A contains fields: Key / Element name / atomic weight / etc...
Table B contains fields: key / Date (daily) / Gold price / Fe price / other commodity price of the day - all material i try to track..

Basically, Table B is the daily price of [Element] field of Table A. So how can I relate table A and B ---- I need use Value of the [Key] of Table A as element field of Table B. When I add new commodity to table A, would like access automatically add a corresponding field in table B and start to track the price of that commodity.

Is it possible in access? Is there a name for this kind of implementation?

 

[John Big Booty]

Your table structure should look like;

TBL_Element
ElementID (Primary Key)
ElementName
etc.

TBL_PriceHdr
PriceID (Primary Key)
PriceDate

TBL_PricesDtl
PriceID (Foreign Key)
ElementID (Foreign Key)
Price

You would have a main form that is bound to your table TBL_PriceHdr, the form would have a Subform bound to TBL_PricesDtl the two forms would be linked via a parent child link of the PriceID field in the two tables/forms. The sub form would be a continuous form and could contain as many metal prices for each date as you required.

Metals in the subform would be populated via a combo box using the table
TBL_Element as it's Row Source

 

[benzhu]

John:

Thanks for the reply. You provided a good 'standard' access solution.

I am also interested if there is programming structure like following, how to make that fit into database world:

Table_Element:
Key
Element_name
etc
Private key (pointing, access, querry to its own daily price table)

Tables (each element will have its own table)
If I do so, will I break all database rule, or there is a solution provided by access or any other database solution, will allow me inplement this manner?

 

[John Big Booty]

What you are suggesting sounds to be rather de-normalised, so I would tend to avoid it at all costs.

 

[benzhu]

What you means:

The way I want to do is strictly programming domain - database theory does not deal with it, or out of scope?

 

[benzhu]

John: The following is my evolving problem. I have a normalized tables and relation setup, but as project moves on, find my set of table value changes over time:

tbl_mine_project
...
project_name (primary key as auto number)
current_mineral_reserve
... other project atributes like grade, volume etc

at the begining of the design, we only focus on the status (at one point of time, just realized). But as time moves on, the mineral reserve will change and need update. but we would like to keep the history of each reserve reported over the time. What will be best way to modify the above table to achieve history tracking in database design - without excessive database hacking, or best deal it in programming domain?

 

[Pat Hartman]

Access is an application development tool that is data centric. That means, it works best with data stored in databases. If you want to manipulate everything in memory, why not use a different tool that gives you fancier array processing abilities?

By storing all the data in properly normalized tables, you will be able to analyze it with queries.

 

[benzhu]

Hohn, Pat - thank both of you.

Summary - for my first question,:
Tbl_A: Key / Element / etc
Tbl_B: Key / Element ID / Date / Price

for my second problem:
Split into two tables, all static attribute remains in original table, and create second table like this:
Tbl_c: key / Project_ID / Date / all updatable attributes

I guess this can be used in all history/time stamped issue going forward for me - until either memory or performance or security became an issue.

Thanks again.

 

[gemma-the-husky]

what you do not want is a separate daily price table for gold, iron, platinum, chromium etc - or even a separate column in a daily prices table, If you do that, you need to add extra columns, and redesign your database each time you track a different element

repeat. YOU DO NOT WANT A SEPARATE TABLE FOR EACH ELEMENT

what you have is a price table with a 2-field key comprising

date AND element

if you then want to see all the prices on a date, you can
if you want to see all the prices for just copper, you can

or any combination of dates/prices etc.

tracking new elements then needs no additional work at all

it also means you do not have spaces for certain elements on certain days. just add the data for the days you do have. so if you only get a rate once a week for Silicon , say, you don't store blank entries on the days you do not have a price.