Simple table design enquiry

[Guest]

Hi,

I have a table 'tbl_Opt' which consist of

OptionNumber
BIT0
BIT1
BIT2
BIT3

OptionNumber is a primary key and its value is from 1~3

I have another table call tbl_Mem which has ItemID(primary key), ItemName,
etc and each ItemName should have a set of tbl_Opt (OptionNumber 1~3, with
unique values of Bit0~Bit3). For example:

Item1 OptionNumber Bit0 Bit1 Bit2 Bit3
1 0 0 1 0
2 0 0 0 0
3 1 1 0 1
Item2 1 0 0 0 0
2 1 1 1 1
3 0 1 0 1
.....


I tried to create a 3rd table called tbl_OptSet which has these members,
OptSetID (primary key)
OptionNumber (foreign key)
ItemID(foreign key)

but it seems that I can't get the structure correctly. Can anyone enlighten
me please.

Regards,
Chris

 

[Jason Lepack]

Do this with two tables. tbl_mem is fine.

If you are always only going to have those 4 bits then your design is
ok, but you need to add the item number into the tbl_opt:

tbl_opt:
ItemID
OptionNumber
Bit0
Bit1
Bit2
Bit3

If you think that you will ever add another bit then the best thing to
do would be to use a design like this:

ItemID
OptionNumber
BitID
BitValue

Then querying a specific bit or range of bits is much easier and if
you want to output a list like the one you have shown then you can use
a simple crosstab query to get it.

If you choose the design I listed above then you could add another
table:
tbl_bit:
bitID
bitName

Cheers,
Jason Lepack