.net - ASP.net & SQL 2008: What is the best way to store multiple values for lists when values are of varying amounts and not repeated throughout rows? -

i’m creating web app school using asp.net , sql 2008. have page users view course descriptions. part of course description include list of topics covered. topics different every course , there different amount of topics every course. data bind topics ordered list in repeater. need provide search functionality on web app. elegant , normalized way of storing topics in sql? want avoid writing vb or using client side scripting.

i’ve considered:

•entering values separated commas in field named topics. don’t want have programmatically separate values repeater , i’m not sure if elegant solution db.

•using xml data type field named topics. think may not necessary , more complex needs be.

•creating separate table each course pk , field named topics. don’t want have create tables!

not trying lazy efficient!

when designing databases helps work core objects/entities , see how relate each other. try , have normalized tables, never de-normalize until need to. in above example have courses, topics , users, although above problem doesn't included users, we'll leave 1 side.

my preferred route try , keep solution simple possible @ times. never try , use fancy solution unless absolutely necessary. not use xml it's not needed.

you comma seperated solution not normalized, , cause problems duplication, , adding , removing values.

your seperate table per course not efficient, , creates duplicated table structure, make maintenance hard, expands.

so, problem, how model topics , courses.

i like:

create table topics (     topic_id int identity(1,1) primary key,     name varchar(255),     description varchar(1023) );  create table courses (     course_id int identity(1,1) primary key,     name varchar(255),     description varchar(1023) ); 

this allows create relationships users in courses, sharing topics between multiple courses.

i creating joining table

create table course_topics (     course_id int references courses (course_id),     topic_id int references topics (topic_id),     primary key (course_id, topic_id) ); 

you can use these tables, , bind data directly controls in .net, solving problem.