Database Design question for those with experience

[Mr. Nice Guy]

This question has to do with my actual database design. I'm new to databases (as most of us are), and not very good at analysis and designing the structure yet, so I was wondering if someone with a little experience could help me out.

My database will be used for storing Test Requests submitted by Engineers. There are three types of sub-test for each test, Rate tests, Durability Tests, and Creep Tests. A Rate/durability/creep test can consist of a number of "Static Deflection", and/or "Dynamic" sub-sub-tests, and the only thing that differentiates the three types of sub-tests is that they each have a number of different individual properties. How could i set up my tables so that each test can have a number of rate/durability/creep tests, and each of those could have a number of statics/dynamics? This is what i have so far:

  _________                ____________
|   TEST   |              |   TESTTYPE  |
|testID    | -----        |testTypeID   |        ________
|   .      |      `-----> |testID       |       |  TYPE  |
|   .      |              |typeID       | <---- |typeID  | 
|__________|              |_____________|       |name    |
                                                |________|

(BTW formatting that table was not as easy as it looked)
The testID indicates which test number it is, and type would describe whether its a rate, durability, or creep, which ends up building a many-to-many junction table. the only problem is that i don't know how to go about assigning the custom properties to each sub-test depending on what type of test it is...I'm sure that any of you gurus will know how to go about doing this with all your vast experience. Well at least i hope so cause I wouldn't want to go drawing all these pretty tables for nothing...

Thanks to everyone who considers it anyway.

Regards,
CB

 

[Ziggy]

I thought I'd give it a shot, I'm not famailiar with with your data but see if this makes sense.

TableTest

TestId
TypeID
PropertyID
TesterId


TableTester

TesterID
Tester


TableType

TypeId
TypeDescr


TableProperty

PropertyId
PropertyDescr


Relationships

Table test is the main table with the others related to their corresponding ID's. from the main table I set ut up so each field is a "Lookup" to select from the other tables, assuming all info is predefined.

I'm not sure how to get a snapshot into the post to show the relationships.

Hope it gives you some Ideas.

Ziggy

 

[Mr. Nice Guy]

Hey fellow Ontarian,

Yeah that's kind of the direction I was heading, but the only problem is that the TableProperty table would need to have different fields depending on which test type it is, since each test has different properties...

That is why I'm stuck because I need some way to set it up so that if a test is a Rate test, it would have certain parameters, which would be different parameters from a durability test

I don't know maybe this is a sign that I should have just been a professional beachbum...

 

[Ziggy]

Hi neighbour

Don't despair where there's a "Will" You'll find the Way...

Ok so drop the property table and include a property field in the "Type" table. That seems to make more sense.

The Test table is were you are logging the tests, the type of test is selected from the "Type" table and within the type you assign your parameters.



TableTest

TestId
TypeID
PropertyID
TesterId


TableTester

TesterID
Tester


TableType

TypeId
TypeDescr
Parameter

 

[Mr. Nice Guy]

Yeah hopefully that will help. I'll see what i can do. Thanks!