database design to account for unknown amount of info 1

[theotrain]

im trying to design a database that is (for me) somewhat complex. i hope someone with some experience can help me out.

imagine you have a table of customers. for each customer you need to track how many tests they've taken. also, for each test you would want to track which section they are on, score so far, and other relevant info.

for that reason, i assume each test would have its own little table, just to save its state for that particular customer.

in addition, the number of tests that we would have to catalogue for each customer is unknown (realistically we might say no more than 100)

so im having trouble figuring out how to design a database for this purpose. what exactly do i put into the "customers" table to attach all these little "test status" tables that are going to be tacked on as needed? i may be thinking about this all wrong, but my limited database experience is not providing me with any answers.

please help! thanks

 

[Remou]

Have a look at "At Your Survey" available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[fneily]

You have a many-to-many relationship: one customer can have many tests. One test can be done by many customers. Relational database dislike many-to-many relationships. So you'll change it into two one-to-many relationships by creating what is known as a junction table. So you'll have three tables: tblCustomers, tblTestInfo, tblCustTest
tblCustomers has info on the customers.
tblTests has info on the tests.
tblCustTest looks like this:
CustTestID CustID TestID Section Score Date etc.

It has the primary keys of the two tables has regular fields. And it has any COMMON data such as date, score, etc.

You can then pre-fill tblCustomers and tblTests. Then on the entry form for tblCustTest, you can comboboxes to select CustID and TestID to speed entry.

Then, using queries, you can do any analysis you want.

 

[theotrain]

i get it. you have to design a new table so that basically every unit of info coming in can be added as a row to that table. i was having trouble visualizing where to stuff all the data, but now i see perfectly.

great explanation, thanks!

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886