D
Dave Schoeff
I have a database which stores information about courses. This Course table
structure is very simple: CourseID, Description, Title, etc. I need to
store other information about the courses, but this information is more
irregular. I was thinking about creating a CourseAttribute table with just a
few fields: CourseID, AttributeType, AttributeValue, SortOrder. If I need to
store books needed, it would be in Attribute records: CourseID, AttributeType
= "Book", AttributeValue= bookID. A course may have several books, or no
books. If I need to store lists of Course Objectives, each objective would
have a record in the Attributes table: CourseID, AttributeType = "Objective",
AttributeValue= text of the objective. I'm looking at an attribute-oriented
solution because my users keep coming up with new attributes for the courses
(test questions, vocab, etc). I don't want to keep adding new specialized
tables -especially since some ayttributes are unique to particular courses.
My design makes sense, until I try to aggregate the data about a course by
combining the Course record and the attribute records. In SQL, I would
probably use a cursor to assemble all of the attributes into a single
value(probably a comma separated list). I'm currently using VBA to solve
this problem in Access, but there should be a more elegent solution. --
Dave
structure is very simple: CourseID, Description, Title, etc. I need to
store other information about the courses, but this information is more
irregular. I was thinking about creating a CourseAttribute table with just a
few fields: CourseID, AttributeType, AttributeValue, SortOrder. If I need to
store books needed, it would be in Attribute records: CourseID, AttributeType
= "Book", AttributeValue= bookID. A course may have several books, or no
books. If I need to store lists of Course Objectives, each objective would
have a record in the Attributes table: CourseID, AttributeType = "Objective",
AttributeValue= text of the objective. I'm looking at an attribute-oriented
solution because my users keep coming up with new attributes for the courses
(test questions, vocab, etc). I don't want to keep adding new specialized
tables -especially since some ayttributes are unique to particular courses.
My design makes sense, until I try to aggregate the data about a course by
combining the Course record and the attribute records. In SQL, I would
probably use a cursor to assemble all of the attributes into a single
value(probably a comma separated list). I'm currently using VBA to solve
this problem in Access, but there should be a more elegent solution. --
Dave