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.
