Linked tables

• james.bennett

  Grasshopper

  Points: 21

  More actions

  November 18, 2008 at 8:04 am

  #202954

  Hello, newbie here, sorry if I violate any protocols, I am trying to figure out how to use two tables to store multiple records in an MS SQL database and need some guidance...

  I have serialized objects which will have several hundred test data points associated with each. I wanted to store top level info on the main table such as model number, date tested etc., and then create a relationship to a table by serial number that will contain the 401 data points for that DUT. Is this a good way of doing this?

  I just don't want the main table to have 401 rows of data for every serial number, instead, I would like the data points to be in a secondary table...

  Thanks in advance for any help,

  Jim

• Mike John

  SSCertifiable

  Points: 7216

  More actions

  November 21, 2008 at 1:34 am

  #901484

  Very difficult to give a full answer without knowing a lot more about your data, but it sounds like you ahev got the principles right. If you post the create table statements for what you have so far it will be easier to comment.

  Mike John

• David.Poole

  SSC Guru

  Points: 76035

  More actions

  November 30, 2008 at 8:08 am

  #905247

  Sounds like you want a 3 table design.

  1. A "Tests" table holding your header information

  2. A "Measuring Points" table holding that items that could be applied to each test.

  3. A mapping table that associates any test with measuring points and stores the relevant data against the mapping.

  That way you have one test, a single set of 400 measuring points and only those mappings that apply to the combination of the two.

  LinkedIn Profile

• james.bennett

  Grasshopper

  Points: 21

  More actions

  December 2, 2008 at 8:56 am

  #906278

  Thanks for the info, let me elaborate on what I have so far...

  I won't have any scripts to create the tables, IT has to create them since I don't have the privaledge to do so.

  My current design has two tables:

  Events_table:

  1. ID (primary key)

  2. TestName

  3. TimeStamp etc, etc.

  Secondary table is:

  Test_Event_Data:

  1. ID (primary key)

  2. EventID (duplicates allowed)

  3. Data

  the Events_table has a one-to-many relationship to the Test_Event_Data table where ID in the Events_table is linked to the EventID field of the Test_Event_Data table

  This should enable me to store 401 data points per test event, in other words, every test event logged in the Events_table should have 401 rows associated with it in the Test_Event_Data table...

  I will then retrieve my 401 data points based on a single test event...

  Does this sound reasonable for retrieving test data from past tests?

  I have other tables linked in the same manner that contain things like test parameters, test criteria etc...

  Thanks

  Jim