Database table person - Kosudo/nextSIS GitHub Wiki

##Rationale to having a Person entity/table A person of course is one of the most important entities in our data model and it's also the site of one of our most important design decisions, which is to say 'people' are one entity and we won't break these down into role-based entities (and ultimately tables) for student, teacher, parents, administrators (as in administrative staff) and so on. This represents a conceptual shift from some student information systems, but the problem of defining people by their roles is that it's easy to get stuck to them.

When you get stuck in a role-based entity situation with a student information system, conceptually you are saying that a person is either a teacher or a parent, and you store their data in that table. But what if a person is both a teacher and a parent? (In fact there are multiple examples of this at [Busan International Foreign School] (http://www.bifskorea.org)). A teacher might also be an administrator, and you might even have a parent who is both an administrator at the school and a teacher.

In holding a person as an entity conceptually, it enables us to say - as data model design appears to properly dictate - that a role is at least an attribute of the person entity - or more properly an entity in itself to which certain exclusive role-based attributes are associated.

##Login information

I've associated login attributes directly with this table. You can make a case for separating out into a group - effectively making it its own entity - which would then ultimately support a person having multiple accounts. However, as long as a person can have multiple roles (a separate entity), I can't see the need for it. I'm marginal on this decision so feel free to argue for a change if you see a reason I've missed (which is entirely possible).

You can also make a marginal case for creating a separate 'login' table with a one-to-one relationship with 'people' for security reasons, on the basis that such a table would be accessed less than the 'people' table and this would minimise the risk of SQL injection attacks, but let's assume the code will ultimately be secure enough to make this step unnecessary.

##The fields

###id (primary) I've made this an unsigned INTeger which gives us over 4.2 billion values ([2^32 − 1 or 4,294,967,295 specifically] (http://en.wikipedia.org/wiki/Integer_(computer_science))). I know that's got to be overkill as an unsigned MEDIUMINT at 16 million records should have sufficed, but I'm being ultra-cautious here. This can be used as the unique identifier for all people within the system, but a local_id field is also provided.

###local_id Ideally, you'd start off using nextSIS as a clean system with no preference as to the method of uniquely identifying a student or staff member. In this scenario, the id field (the primary key) could be used as this identifying number, or if the school has an existing purely numeric id system (after import). However, where a school uses an alphanumeric code this would not be possible, so the 'local ID' field is provided for this purpose, but it can be omitted, so the value can be NULL.

###surname Stores the surname of the person. This allows NULL values at the database level in order to support people known by [mononyms (single-names)] (https://en.wikipedia.org/wiki/Mononymous_person) - such as people in some countries (notably India and Indonesia). An index is created on this field.

###first_name Stores the first name or mononym (single-name) of the person. NULL values are not allowed.

###middle_name This is the middle name of the person. NULL values are allowed.

###common_name This is the name the person is generally known by - which may differ from their other names.