Help with deciding on database structure

Perhaps this will be an easy one for some of the folks here.

I have been tasked with coming up with a better system to maintain a set of requests for testing in our company. It doesn't have to be extraordinarily elaborate but there are certain features I would like to have.

Basically, I would like to have someone be able to use the web (perl scripts) to submit an image for testing. Their request would be entered as a unique ID in the REQUESTS table.

Email would be sent to me telling me there was a new request and I would then go to that request and assign it to the various testbeds that should run it.

When it is assigned, the testers must test it and an entry is created in a results table.

What I can't decide on though is, do I need 3 tables or 4.

I was thinking 3, REQUESTS, RESULTS, TESTBEDS where REQUESTS would have a REQUEST_ID, some other information and a TESTBEDS field.

TESTBEDS would contain all the pertinent information about the testbeds including a TESTBED_ID.

When a test was assigned a new row in the RESULTS table would be created that would have the REQUEST_ID and the TESTBED_ID.

Then I could search on the REQUEST_ID and find all the TESTBEDS that ran it.

However, the only way I know of to enter the TESTBEDS into REQUESTS is by: '0001, 0002, 0003' which won't work for a query like:

SELECT * FROM REQUESTS WHERE TESTBEDS = '0001'; I won't get anything back.

My next thought was to add an ASSIGN table containing just two columns: REQUEST_ID and TESTBED_ID.

Then, the user would enter the requested testbeds in the REQUEST, I would then ASSIGN the REQUEST_ID to TESTBED_ID with a new row being created for each testbed assigned to the request.

Searching would then be:

SELECT * FROM ASSIGN WHERE REQUEST_ID = 'requestid';

It would return all testbeds and I could get more information from there.

Does this make more sense? Is there a way to get the information out of the first method that I need?

Thanks,
Rick Received on Wed Feb 17 1999 - 04:41:18 CET