Conditional relationships

[Mr. Nice Guy]

Hi everybody,

I've new to databases, so I'm having a little trouble determining how i should set up my design, and would really appreciate the perspective of someone who is experienced (since it would probably be quite easy for them). first some background,
my database is basically built to store test requests. Each Test request may or may not have:
1 or more "Rate" tests
1 "Durability" test
1 "Creep" test

the Rate test will have:
1 or more "Static" Tests and/or
1 or more "Dynamic" Tests

the Durability test will have:
1 or more "Static" and/or
1 or more "Dynamic" tests and/or
1 or more "Other" tests

The Creep test will have its own definition (which isn't as complicated)

These 3 tests also have other criteria which differentiates them from one another.

I have determined that I need a "Request" table, a "Test" table, and a "SubTest" table, however i dont know how to relate the tables so that if The test type is "Rate", and the subtest type is Static, the child table will have "static" properties, and if it is "dynamic" the child table will have "Dynamic" Properties (and same for "Durability" tests).

does anyone have any ideas on how i should get started? i'd really appreciate it.

regards,
CB

 

[RichMorrison]

Sounds like you want something like this:

1) One "Request" table and use an Autonumber as the primary key.

2) Three separate "Test" tables; one for Rate, one for Durability, and one for Creep.

3) For the three types of Test tables, one Static and one Dynamic "sub test" table. That sounds like 6 additional tables.

The primary Autonumber key of the Request table will glue all the other tables together. Since you may have 0, 1, or many Test types AND 0, 1, many sub test types, you will use queries with left joins to link Request to Test and Test to sub test.

HTH,
RichM

 

[Mr. Nice Guy]

So I should Relate the Parent Request table to 3 child Test tables? I didn't know if I should join more than one child to a parent. I also am a little confused about all the different join types, and when to use them. Just to clarify, I should not join the tables in the "Relationships" windows, but just do it with queries later on? Thanks for the help and patience.

 

[RichMorrison]

I join tables in queries. I do not use "relationships". That's an old habit but it works for me.

See Access Help on "join". You will find examples of left joins.

You will join the Request table to the 3 Test tables via the Autonumber that is the primary key of Request. That means that when you create an entry in any Test table, you copy the primary key of the parent Request row.

You will join each Test table to the Sub Test tables with the same Autonumber.

So the Test and Sub Test tables will *not* have a true primary key.

RichM

 

[Mr. Nice Guy]

Thanks a lot for all the help, I'm starting to get the hang of it i think, and the joins are making sense now...

I've attached a picture of my design if you would like to see it. My main focus is in the top right corner with the Tests and subtests.

The Tests will work very nicely for my purposes, however I just have One more question about this structure. See for Rate tests and Durability tests, they can each have zero or more static and dynamic tests, which is no problem, but how can i relate the subtest to a UNIQUE test key in the case where one request has a rate test, and a durability test (both with the same ID) and both of those have static subtests? should i make a primary key from 2 test fields? Thanks again

 

[RichMorrison]

You wrote
<<
See for Rate tests and Durability tests, they can each have zero or more static and dynamic tests,
>>

On the diagram, I don't see any relationship between Durability and Dynamic/Static.

As to relating a Request to a Rate to a Static and/or Dynamic,
your key structure should do this.

RichM

 

[Mr. Nice Guy]

In my diagram I didn't make a relationship with the durabilities because I didn't know which field to relate or how to make a unique key.

As for the whole "relationships" window, I can see where you're coming from. As the database gets more complicated, it seems better to just join all the tables with queries than do this relationship garbage. The reason for my diagram is so I can see which tables all need foreign keys etc.

I think my design is coming to completion, and I think I understand what I have to do now thanks to your guidance though.

Thank you for all your help Rich

I owe you one