Hey all!

I'm new to relational db design and I've run into a problem. Here is the scenario:

I have a table that contains three fields (well, it contains more than that, but three of which this problem is concerned with). Each of these three fields can have one of two values. However, trying to keep data integrity and reduce redundant data, I am unable to relate these fields to a table.

Table1:
tblID <pk>
... <other fields>
Field1
Field2
Field3
... <other fields>

Table2
tbl2ID<pk>
Field4
Field5

The idea is that Field one can equal the contents of tbl2ID 1 or 2 (being the two possible values) as well as Field2 and 3. I've tried linking tables but it still doesn't produce a unique way for me to display the data in fields 1-3 in my code.

Is this as clear as mud?

Ark

It certainly is! Are you saying that Table2 defines the possible values for Field1, Field2 and Field3? If so, the PK of Table2 should be the actual value, not an arbitrary "ID". For example, supposing the 2 values are Male and Female:

Create table Table2
( gender_value varchar2(6) primary key
, ... );

Create table Table1
( ID int primary key
, Field1 references Table2
, Field2 references Table2
, Field3 references Table2
);

insert into Table2 (gender_value, ... ) values ('Male', ... );
insert into Table2 (gender_value, ... ) values ('Female', ... );

Now field1, field2 and field3 values are constrained to be Male or Female.

Of course, you don't require a Table2 to do that:

Create table Table1
( ID int primary key
, Field1 varchar2(6) check (field1 in ('Male,'Female'))
, Field2 varchar2(6) check (field1 in ('Male,'Female'))
, Field3 varchar2(6) check (field1 in ('Male,'Female'))
);