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 a web app for a school using ASP.net and SQL 2008. I have a page for users to view course descriptions. Part of the course description will include a list of the topics covered. The Topics will be different for every course and there will be a different amoun开发者_运维技巧t of topics for every course. I will eventually data bind the topics to an ordered list in a repeater. I also need to provide search functionality on the web app. What is the most elegant and normalized way of storing those topics in SQL? I want to avoid writing VB or using client side scripting.

I’ve considered:

•Entering the values separated by commas in a field named topics. I don’t want to have to programmatically separate values for the repeater and I’m not sure if it is an elegant solution for the DB.

•Using the XML data type for a field named topics. I think it may not be necessary and more complex than it needs to be.

•Creating a separate table for each course with a PK and a field named topics. I don’t want to have to create all those tables!

Not trying to be lazy just efficient!

When designing databases it helps to work from the core objects/entities and then see how they relate to each other. Also try and have normalized tables, never de-normalize until you need to. In the above example you have courses, topics and users, although the above problem doesn't included users, so we'll leave that to one side.

My preferred route is to try and keep the solution as simple as possible at all times. Never try and use a fancy solution unless absolutely necessary. Thus I would not use XML as it's not needed.

You comma seperated solution is not normalized, and will cause problems with duplication, and adding and removing values.

Your seperate table per course is not efficient, and creates a duplicated table structure, which will make maintenance very hard, as it expands.

So, back to the problem, how do you model topics and courses.

I would do something 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 you to create relationships to users in courses, as well as sharing topics between multiple courses.

I would do this by creating a 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 then use these tables, and bind the data directly to controls in .net, solving the problem.

Your option 3 would be normal way to do this in a relational database. There should only be one table needed to hold all the topics - your schema would be something like

CREATE TABLE Course (
   CourseId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Course PRIMARY KEY
   -- Other fields
)

CREATE TABLE CourseTopic (
    CourseTopicId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_CourseTopic PRIMARY KEY
    CourseId INT CONSTRAINT FK_CourseTopic_Course FOREIGN KEY REFERENCES Couse(CourseId),
    Topic NVARCHAR(50)
)

So you could have many topics for each course. Only 2 tables total. If you have large number of shared topics, you might have a separate Topic table, and then CourseTopic would just hold the id of the course and the id of the topic, while the text of the topic would go in the Topic table, but it sounds like that might be overkill in this case.