Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Fort Worth, TX
    Posts
    1

    Relational design question

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Relational design question

    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'))
    );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •