PHISSUG

How do you design your references like (color, salary level, etc)? Below are various approaches one can use. Which one do you prefer and why:

   approach #1: Apparels (apparels_pk, description, color as varchar). e.g. color entries are "blue", "red"
   approach #2: Apparels (apparels_pk, description, color as int). e.g. 1=blue, 2=red
   approach #3: Apparels (apparels_pk, description, color_pk as FK).

are there any other approaches you can think of?

 

I use Approach 1.

Approach #1. With the ubiquity of databinding, this one gives the least amount of coding. In short, this one is directly "sensible".

Approach #2. Anyone can easily mess up with the semantics of your columns on a design like this. Column value should directly convey it's meaning unless it's a foreign key.

Approach #3. This is supposed to rectify approach 2. Honestly, I haven't encountered any scenario that would compel me to do this.

I choose approach #3: Apparels (apparels_pk, description, color_pk as FK)

Explanation: As a rule in 3NF. all non-key elements must be directly dependent on the PK and not directly dependent on any other data elements within the same entity, with the exception of the alternate key. although sometimes this can have a effect on the query performance, that's why we need to denormalize the data model to achieve the maximum query performance.

---

CBIP - Data Analysis and Design

sorry guys .. forgot what prompted me to start this thread almost a year ago.

but anyway,for maximum flexibility, it should be #3. for convenience, I will pick #1.