table structure for dynamic columns

[wbodger]

Wasn't sure if this should go here or in Setup and Administration, but it seems this is the right place, so here goes. I am setting up an experiment DB. The experiments use devices that have different properties (most notably number of injection points). So, within the experiment inventory I will have experiments that have anywhere from 4 injection points to 12 injection points. An each injection point has 6 characteristics. So, I have an experiment table with some IDs including device_id and other info, a device table which tells how many injection points, then an experiment_inventory table. This experiment_inventory table has those 6 characteristics for each injection point (the variable number). My question is thus, is there a nice way to handle the dynamic nature of the number of injection points aside from having characteristica1...characteristica12, characteristicb1...characteristicb12 etc., where any unused injection points are just left null?

For instance Experiment A uses Device 8B, which has 2 injection points whereas Experiment B uses Device 3A which has 10 injection points. Now, in the experiment inventory (where I track what was injected, volume, concentration, markers leakage etc.), I have to track those characteristics for 2 injection points (each discretely) and 10 injection points. I know I can make a larger number of fields and just leave unused fields NULL, but I was wondering if anybody had another solution>

Thanks!
wb

 

[gmmastros]

I would normalize this. Something like...

InjectionPoint
--------------
InjectionPointId (identity)
WhatWasInjected
Volume
Concentration
Markers
Leakage

ExperimentInjection
-------------------
ExperimentId
DeviceId
InjectionPointId


This way, you can have multiple Injection Points per Device, and multiple Injection Points per experiment. The data in the ExperimentInjection table would be something like:

ExperimentId DeviceId InjectionPointId
------------ -------- ----------------
A            8B       1
A            8B       2
B            3A       3
B            3A       4
B            3A       5
B            3A       6
B            3A       7
B            3A       8
B            3A       9
B            3A       10
B            3A       11
B            3A       12

Of course, I wouldn't actually store the experiment name or device name, I would have an integer ID in each of those tables and store the ID in this table.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Yup, that makes sense. I was stuck halfway between Excel world and SQL world and couldn't see my way through. This is the way I was trying to see, thank you!

wb