Relationship between two tables

[dfuas]

Hi,

I have a db with two table.
Table1 Trade has the following among other fields: RefNo (autoNo) PK, TradeDate, HostName, Methodology, etc.

Table2 Market has the following among other fields: MarketDate as Date ( ), USD/EUR_Rate, USD/CHF_Rate, USD/BRL_Rate, etc.

I need to link both tables so that the TradeDate correspondes to the MarketDate. what is that say on TradeDate if it is on 15/01/06 I can see what the Market values for the MarketDate corresponding to that TradeDate.

I tried to link MarketDate as PK on Market table2 to TradeDate on Trade Table1.
It seems to work but it does not seem right as sometimes I get errors in entering data, etc on the Market table2.
The MarketDate is unique meaning only one data of values for a given date, they can be modified but not duplicated.

Can anyone help, on the best solution for the links to work?

Thanks
dfuas

 

[Len Boorman]

Are you sure that you need two tables. Seems that there is a 1:1 relationship here perhaps and in that case there is a question over having two tables

Len

 

[dfuas]

the MarketDate (on table2) as date ( ) is entered only once per day.
The TradeDate (on the table1) with RefNo as PK is entered manually on any day.
So you may have MarketDate (as this is daily) without TradeDate as there was no Trading on that MarketDate.

Thanks
dfuas

 

[Len Boorman]

Okay
On the Market table the Market Date needs to be the PK. (1 Entry per day)

Now on the Trade Table will you have more than 1 entry on any specific day

If only one entry per day then Trade Date becomes PK and you have a 1:1 relationship and question again why two tables

If there is more than 1 entry on specific day then another PK (perhaps the autonumber) would be suitable,

The join then goes From Market Date in Market Table to Trade Date in Trade Table. make sure you go in the right direction. Also ensure that you have precisely the same data types and format.

If yoiu still get a problem then you need to spell out exactly what happens with the actual data you are trying to enter, where and what the error message is .

Len

 

[dfuas]

That exacly what I have at the mom. There is only 1 entry on a specific day on the Market Date, but when I try to update or change the values in there, first they don't show on the form I use to enter the data and so I can't update them.
If I try to enter a new data I get a error but that should be normal as only 1 entry on a specific date, but I should be able to see that data when I open the form, so that I can change some of the info. if required.

thanks
dfuas

 

[Len Boorman]

The form you are using to add data.

If you are opening it in Add Mode then it will not show existing entries.

To edit existing entries you need to open it in Edit mode and possibly the form needs to be continuous.

You may get an error if you try to change a market date where you also have a trade date if you have referential integrity on and cascade update off.

Len

 

[dfuas]

Ok, I can see existing entries and I can update them, but if I try to enter new values (records say by mistake, although I should not in reality), I get errors because of the relationship and it messes up and then I cannot see again the existing entries, until I go to form design and change the Data entry property to No.

Also the relationship does not allows me to enforce referencial integrity. I get an error that data in the table Trade violets referencial integrity rules, etc.

Sorry but I don't know the <> between Add Mode and edit Mode
your help is appreciated

thanks
dfuas

 

[Len Boorman]

That makes things a lot simpler.

If you cannot enforce referential integrity then the data is of no use whatsoever and you need to sort out the data first. A Referential Database where Referential Integrity is not enforced is basically not worth a candle.

So sort that bit first.

Second stage use a separate form for entry and edit

But again think about why you have two tables. I am not at all sure that two tables are required or desireable.


Will you have more than one entry for a specific date in the Trade Table ?.

Len

 

[dfuas]

In the Trade Table I could have any entry depending of the day of that trade, so the date is entered manually. I could enter yesterday info. and enter yesterday date.

The Market table Market Date is for the market information of the day.
So from a trade date you can see what the market xchange rates are for example.
If I could put that on the same table would make life easier but I can't see how. For a differente trade date there may be a differente market info.

The trade table has a autonumber PK Refno, could that work if I put it as FK
in the Market table?

thanks
dfuas

 

[Len Boorman]

In the Market Table you have a PK of Date. That means you can have one line of data for that Date.

You could put in Market Data for each date of the week and come back later and "Add" the Trade data. The Add would actually be an Edit of the exitsing row of data. So you could come back a week later and edit the particular row controlled by the Market Date and edit the missing values to whatever you want.

"The trade table has a autonumber PK Refno, could that work if I put it as FK
in the Market table?"

You need to think through what you want to achieve.

From my perspective you want Market/Trade comparison data on a daily basis.

There will be one set of market data for a specific day.
There will be zero or one set of trade data for a specific day.

If these two statements are true then I cannot see whay you need two tables. Okay over time you may have records where only Market Data is present with nulls for the trade data because there were not trades. Okay not really a problem. You could run a delete query say weekly that deleted all records where the Market Date was older that say 30 days and a trade data columnor columns was/were null.

Relational Database design is based upon mathematical modelling. You have tables for reasons not just because it is thought they should be there.


Len

 

[dfuas]

From my perspective you want Market/Trade comparison data on a daily basis.
There will be one set of market data for a specific day.
There will be zero or one set of trade data for a specific day

Ok, these statements are true.

How would I achieve this in one table? I see what you mean but maybe an example? I am still confused.

Thanks a lot

dfuas

 

[Len Boorman]

How about a table with fields


MarketDate PK,
USD/EUR_Rate,
USD/CHF_Rate,
USD/BRL_Rate,
etc
Trade HostName,
TradeMethodology,
etc.

Since you exchange rates are all USD based you could go

MarketDate This is your Primary Key
EUR_Rate,
CHF_Rate,
BRL_Rate,
etc
Trade HostName,
TradeMethodology,
etc

Len

 

[dfuas]

So you mean that I could come back say a week later and input the Trade date into that table?

dfuas

 

[Len Boorman]

So you mean that I could come back say a week later and input the Trade date into that table?

dfuas

Basically Yes. What you actually are doing is editing a field/s. The data row already exists with some blank fields (Trade Data) so you are entering data into those fields.

Add generally means creating an entirely new record

L

 

[Len Boorman]

So you mean that I could come back say a week later and input the Trade date into that table?

dfuas

Basically Yes. What you actually are doing is editing a field/s. The data row already exists with some blank fields (Trade Data) so you are entering data into those fields.

Add generally means creating an entirely new record

L

 

[dfuas]

I tried that, but the problem is that I cannot add more than one record with the MarketDate as the PK.
Sorry but I could have zero or many set of trade data for a specific day. These are referenced by the RefNo autonumber.

dfuas

 

[dfuas]

Sorted, thanks a lot.