[RESOLVED] table design

Hey all, I need a bit of help designing a couple of tables to make sure I have what I need at the end of time.


I have a table (tbl1) with a primary key (id) and a number of fields (30+) . Many of these fields are static fields that I can get with a simple select statement, but about 10 of these fields need different values based on another variable (named OP).

For example if OP = "X" then when I fetch the data for I might get something like this...

v1,v2,v3,ex1,ex2,ex3

However, if OP = "Y" I might get something like this...
v1,v2,v3,whY1,whY2,whY3

Now I suppose I could just create unique rows for everything, but that would yield a large amount of redundant data.

What I am considering is to have 2 tables. The first table would have the 30+ fields and the other would have a foreign key that points back to a particular row in the first table and another field that holds the OP value. That seems like a reasonable way to do this right?
If so, I am quite stuck on how the select statement would look.
It doesn't seem that a join would be used for this, but I am really not sure.

Can someone please let me know if my approach is sound and if so, show me how to construct the select statement for this?
thanks
kevin

If I have understood correctly, tbl1 should have the primary key, and the 20+ static fields.

The second table should contain the value of the the primary key from tbl1, the OP value, and the "about 10" fields that vary based on OP. You should probably set a primary key on the first two fields (assuming that each row of tbl1 should only have one set of values for each OP value).


The Select statement will then be quite easy, as the join will be on the primary key of tbl1, and the OP value (which you could put in the Where clause).

yes you have it correct, and sometimes it only takes writing and rereading a post to figure it out....

thanks si