0

I have an entity: Participants. One of the attributes for participants is Interest. There are 4 valid values (db, ia, hit, other). If 'other' is chosen I need to be able to capture that description and I can't seem to figure out what the best way to do that would be?

I would have a validation rule for Participant Interest that will have the above 4 values. Can I place a description in the entity (see below) though I know that most of the responses will be one of the first three or should I create a separate entity that will capture other and has an identifying relationship with Participant? 

    Participant
    Participant ID
    Participant First Name
    Participant Last Name
    Participant Interest {db, ia, hit, other}
    Participant Other Interest Description


    Other
    Other Description

I'm looking for what is allowed as well as what would be best practice as I have several entities with similar format.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user2163396
  • 1
  • 1

1 Answers1

0

I find this dilemma in every db design and in my experience is better to go for your first option and have everything in the same table. It is not perfect because you'll have nulls for many rows but it will make your query easier and faster( you won't have to do a join), will reduce complexity (one less table) and won't affect storage space (if you define the other column as varchar it will def not take any space if it's null).

For some complex cases I would create an extra table to implement 1-0..1 relations but for this trivial case I'd say it is better just to create a nullable OtherDescription attribute in the main table.

Similar discussion in:

Normalization of an 1:1 or 1:0 relationship

Community
  • 1
  • 1
TonyS
  • 238
  • 3
  • 9