One Relationship only

[reddevil1]

Please could someone help me with my Relationships!!!

I think there is something wrong with the Relationship between tblFinance (where all the money transactions in and out are recorded) and tblContract????

Currently the tbl.Contract.ContractID (PK) is a one-to-many relationship with tblFinance.ContractID (FK)
A Contract can have more than one FinanceID (so this seems to be OK)?

But I also think that instead, maybe I should be creating a FinanceID (FK) in tblContract.
Then should I link tbl.Finance.FinanceID (PK) as a one-to-many relationship with tblContract.FinanceID (FK).

Or are both options the same thing?

 

[Martins]

Hi there,

You already have a one-to-many relationship established between tblContract and tblFinance, which looks good at first glance.You can't use FinanceID as a foreign key in tblContract because there are many transactions for each one contract. So, storing the FinanceID for each transaction would result in duplicate ContractIDs residing in tblContract- which would be a most disagreeable situation.

I hope this makes sense.

Regards,

Martins

 

[jdraw]

What do you think isn't right?

I think there is something wrong with the Relationship between tblFinance .....

Perhaps you need to have different contract types.

You know your situation better than we do so you will have to tell us what isn't right. When you do queries or reports, is there something glaring that's causing concern?

 

[reddevil1]

many thanks for your input, guys.

I didn't think something was right because the tblFinanceID primary key does not have a Relationship to any other table.

And I thought if I added a "FinanceID foreign key" to the Contract Table, then this "field" in the Contract Table will just remain empty - it will never be populated. I wondered if this was OK and if it is normal? Is the only reason for doing this, so that a relationship can be created between the two tables?

 

[jdraw]

Do your test queries/reports etc give you the results you expect?
Is there something or some combination of facts that you can NOT retrieve/calculate from your current database structure?
Do you have test data? Did you test your database to ensure what actually happens is what you expected to happen?