Member Avatar for skof

Hi,

I have just a little experience in databases and I'm looking for help.
I'm designing the test database. The nature of test is to send data file to the device under test, get devise's response and compare it with expected response. For the same test the expected response and the data file may be different depending of the device under test or depending of device's options.
So, I created something like that:

Test_Data
			================
			Test_DataID (PK)
Files			TestID (FK)		Responses
========			DeviceID(FK)		===========
FileID(PK) -|--------<-	FileID(FK)		ResponseID (PK)
FileCRC			ResponseSetID	->---<-	ResponseSetID
TotalResponses		Dependency1		ResponseNo
			Dependency2		ResponseValue
			Dependency3
			Dependency4

to be continued...

  • 1 Contributor
  • 1 Reply
  • 91 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by skof
Member Avatar for skof

TotalResponses is in the Files table because the number of repsonses is actually defined by the file, but values of responses may be different for different Devices.

For each file my Device Under Test may send zero or many responses. The response is a number.

Test_Data table works as a junction table for Files and Responses. First it is queried with known TestID and DeviceID to get subset of data applicable to the selected test for the selected device.

Each Test_Data contains one File.
Each File is contained in zero or more Test_Data.
Each Test_Data contains zero or more Responses (which grouped into ResponseSet).
Each Response is contained in zero or more Test_Data.

So, I have one-to-many relation between Files and Test_Data and many-to-many relation between Responses and Test_Data. Although responses are grouped into ResponseSets, I can not assign a Foreign Key for Test_Data.ResponseSetID to complete my junction table because the Responses.ResponseSetID is not unique and can not be a Primary Key. Using ResponseID in Test_Data table will lead to huge data duplication, there are can be hundreds and thousands responses for one file.
I'd like to break my many-to-many relations into one-to-many.
I suppose it should exist a standard solution for my problem but I just don't know it.

Another problem is Dependencies. That does not look right and each test usually have less than 4 dependencies, 4 is a kind of maximum possible number, but I can not think up something better. One more table? Then it is going to have the same structure and the same problem as Responses table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.