Looking for MSSQL Table Design Sanity Check for Profile Tables with Dynamic Columns.

Posted by Code Sherpa on Stack Overflow See other posts from Stack Overflow or by Code Sherpa
Published on 2012-04-07T05:26:56Z Indexed on 2012/04/07 5:28 UTC
Read the original article Hit count: 169

I just want a general sanity check regarding database design.

We are building a web system that has both Teachers and Students. Both have accounts in the system. Both have profiles in the system. My question is about the table design of those Profile tables. The Teacher profile is pretty static regarding the metadata associated with it. Each teacher has a set number of fields that exposes information about that individual (schools, degrees, etc). The students, however, are a different case. We are using a windows service to pull varying data about the students from an endless stream of excel spreadsheets. The data gets moved into our database and then the fields appear in association with the student's profile. Accordingly, each and every student may have very different fields in their profile.

I originally started with the concept of three tables:

Accounts
----------
AccountID

TeacherProfiles
----------
TeacherProfileID
AccountID
SecondarySchool
University
YearsTeaching
Etc...

StudentProfiles
----------
StudentProfileID
AccountID
Header
Value

The StudentProfiles table would hold the name of the column headers from the excel spreadsheets and the associated values.

I have since evolved the design a little to treat Profiles more generically per the attached ERD image. The Teacher and Student "Headers" are stored in a table called "ProfileAttributeTypes" and responses (either from the excel document or via input fields on the web form) are put in a ProfileAttributes table. This way both Student and Teacher profiles can be associated with a dynamic flow of profile fields. The "Permissions" table tells us whether we are dealing with a Student or a Teacher.

Since this system is likely to grow quickly, I want to make sure the foundation is solid. Can you please provide feedback about this design and let me know if it seems sound or if you could see problems it might create and, if so, what might be a better approach?

Thanks in advance.

profiles

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about dynamic