Relationship check

[Alexander Willey]

Dear Forum,

I am about to set up a database but wanted to check the relationship of the main tables before I add to it. I have attached the relationship design

For a PROJECT, there can be many TESTS, for a TEST, there can be many PRODUCTS

Is my design reasonably sensible?
Would you recommending enforcing referential integrity?

Appreciate any help

Many thanks

Alex

 

[plog]

It looks good except you shouldn't have a ProjectID field in Product. That relationship is established in Test.

Enforcing referential integrity will just make it harder for people to orphan Test and Project fields. Its up to you.

 

[Alexander Willey]

Dear Plog,

Thank you very much for your reply.

If I remove the ProjectID from the Product table and then try to link the field TestID between the Test and Product tables, I get offered an indeterminate relationship, whereas I think I need a 1 to many relationship? See attachment

Am I doing something wrong?

Appreciate your help

Many thanks

Alex

 

[plog]

It should be a 1 to many relationship between the Test table and Product table on TestID.

You might run some queries to verify your data: are there any TestID values in Products that are not in TestID?

 

[Alexander Willey]

Thank you all for your help

The reason for the indeterminate join I think was a problem with how I had set the indexes. I think I have managed to understand and sort this out now.

However, I do not understand the indexes view on the design tab and how to use it. Particularly for my compound keys there seems to be 4 rows of information that I simply don't understand

Are there any good references on how to interpret and use these?

Many thanks again

Alex