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: 191
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.
© Stack Overflow or respective owner