table(s) design for a particular problem

• Christopher Klein

  SSCrazy

  Points: 2648

  More actions

  May 27, 2008 at 6:15 am

  #207750

  Hi there,

  I have a design issue for how to best store data. I have two tables Bench and Index as feeder tables and I need to store the users selection in a 3rd. Assume BenchID and IndexID for the pk of each.

  I need to be able to store different mixed combinations and groupings of each.

  So I need to be able to store a Bench reference to a Bench reference and I also need to be able to store Bench->Index, Bench->Multiple Index,Bench->multiple Index that themselves are tied together and the kicker of Bench->multiple indexes not tied together+multiple indexes tied together.

  When I say 'tied together' its the indexes are assigned a weight factor.

  Oh and at the Bench level, I need to store 3 flags (intFlag1,intFlag2,intFlag3).

  So I might have Bench1 @.6 and Bench2 @.4

  or Bench1 with Index1 @ 1

  or Bench1 with Index1 @ .2, Index2 @.4, @Index3 @.2

  or Bench1 with Index1,Index2,Index3 @ .4 and @Index5,Index6,Index7 @ .6

  or finally Bench1 with Index1 @ .2, Index2 @.2, @Index3 @.2 and @Index5,Index6,Index7 @ .4

  I was thinking I could basically store all of the indexes and weights in a single string but I cannot figure how to group the combined indexes together. Anything I can think of doesn't allow for referential integrity. Also, I'm not sure how I can use the same table to store bench->bench AND bench->index records.

  Any suggestions would be appreciated.

  thanks,

  Chris

• Christopher Klein

  SSCrazy

  Points: 2648

  More actions

  May 28, 2008 at 12:57 pm

  #821401

  nvm ended up using multiple columns... somehow my boss thought that parsing 16,21,12|.1,.11,.11^53,54,55,56,57|.68 was harder than using 4 extra columns *shrug*

  (still cant delete messages)