How to use SQL Server views with distinct clause to Link to a detail table? -

i may total standard here, have table duplicate values across records i.e. people , haircolour. need create table contains distinct haircolour values in group of person records.

i.e.

name      haircolour -------------------- sam       ginger julie     brown peter     brown caroline  blond andrew    blond 

my person feature view needs list out distinct haircolours:

haircolour ginger haircolour brown haircolour blond 

against each of these person feature rows record recommended products.

it bit weird relational perspective, there reasons. build person feature"view add person records using instead of insert trigger on view. gets messy. alternative have person feature view based on select distinct of person table , link recommended products this. have no primary key on person feature view since select distinct view. not updating view. 1 need think how deal person recommendation records when person feature record disappeared since since not based on physical table.

any thoughts on please?

edit

i have table of people duplicate values haircolour across number of records, e.g., more 1 person has blond hair. need create table or view represents distinct list of "haircolour" records above. against each of these "haircolour" records need link table called product recommendation. main issue start creating distinct list of records. should table or view based on select distinct query?

so person >- haircolour (distinct table or distinct view) -< product recommendation.

if haircolour needs table need make sure has correct records in every time person record added. using view automatically, unsure whether can can hang table off view.

if understand correctly, need table primary key lists distinct hair colors found in different table.

create table haircolour( id int identity(1,1) not null, colour varchar(50) null constraint [pk_haircolour] primary key clustered (id asc)) 

then insert records. if querying table called "person" this:

insert haircolour (colour) select distinct haircolour person 

does looking for?

update:

your recent edit shows looking many-to-many relationship between person , productrecommendation tables, haircolour table functioning cross reference table.

as erike points out, opportunity normalize data.

1. create haircolour table described above.

2. populate whatever source like, example insert statement above.

3. modify both person , productrecommendation tables include haircolourid field, integer foreign key points pk field of haircolour table.

4. update person.haircolourid point color mentioned in person.haircolour column.

5. drop person.haircolour column.

this involves giving ability put free form new color names person table. new colors must added haircolour table; colors available.

the foreign key constraint enforces list of available colors. thing. referential integrity keeps data clean , prevents lot of unexpected errors.

you can confidently build productrecommendation table on data structure carry weight.