How to start designing a database?

I will start with what we need to do a thesis, going to do a "System of management students". Want to do a web muzzle, YAP chose python and django framework 1.10. The project is conceived, every parent can go to the website and see grades, attendance, etc of their child. Every school has a Director, teachers, pupils, and all of them are connected to each other. It is easy to implement if you're doing this system for one school, and if the city's 200 schools? And for each school is an individual to do? To create a separate application migration etc in django for each school? In the mind thought of everything, but when I decided to design a DB, it was not easy.

PS I'm not asking you to solve this problem for me. I want experienced people have filed the Council with what to begin? What to do correct? How to implement all of this without pitfalls.
PSS Actually I'm studying for 2 year, still have 2.5 years to diplomcy, you think all is well.

Started to do TK, your almost done.
July 2nd 19 at 17:42
4 answers
July 2nd 19 at 17:44
Start with the study of normal forms of data.
wiki
July 2nd 19 at 17:46
going to do a "System of management students"
- always wanted one!

Sorry, I could not resist, then in the case of:
I can't know Your personal preferences and style of teaching You are used to or think is most true or preferable, but in teaching, I often used the method of "visualization" of what is happening. Quite difficult to explain to someone who's never worked with networks, what is the IP4 package... but when I draw this visually, the perceptual quality is significantly improved.

Actually, what I'm this... Take a sheet of paper or canvas (paint, photoshop, etc.), program for drawing flowcharts, or software to create sketches or something like that and try to draw all of the tables/database objects and the relationships between them. Also, a similar feature exists in many program to work with the database (visualization of tables and their relationships). When You will be able to visually see and represent the objects - it is much easier to perceive what is happening.

An example of life - try to explain to someone what a table of a relational database... if you were to draw an analogy with a sheet from Excel in 95% of cases, the understanding comes almost instantly.

So I can say that the selection at the initial stage, PostgreSQL is not the best idea. PostgreSQL is a very cool database, if You really understand why You need it and why it is. That is, in cases when You have tight "MySQL-pants" and not enough scope for action and hundreds of unnecessary settings to tweak and tweak and enchanting number of options and possibilities most databases - PostgreSQL would be the optimal choice, in other cases, You will curse the world, developers and all things, are constantly faced with some difficulties, which sometimes may even seem silly (though, in 99% of cases it is not). For example, which is only the point that in PostgreSQL there is no "table engines", or not to swap the previously created columns in the table without completely rewriting the entire table, or a dozen indices (and which one to choose?!), against a much more meager amount in MySQL...

My students are often faced with similar problems, according to this, we came to this practice - base design and prototypically to MySQL, there is changing to blue in the face until it is verified do the right vector of development of the database, the code driven around... and then, the project is easily and naturally moved to PG, where he supplied some buns and Goodies PG level (those that MySQL-e no).

I recommend to You, as well as my students - first to design database in MySQL, we usually do this in the program HeidiSQL (free), all very clearly and colorful. Break in Your code and the logic DB, and then, if you really can't wait to migrate to Postgres.

From personal experience, I can say that many choose PostgreSQL, because it(a) "cooler". It is not so, or not so... of the many projects on the PG we only put up one, there is a database amounts to many tens or hundreds of gigabytes, number of tables was close to a thousand, and the number of records in a particular table - the tens of millions. But even now I am working in support project, the volume of data exceeds 1TB, and all lived well on MySQL. According to this, if You choose PG exclusively for some ideological rather than technical reasons - think again.
July 2nd 19 at 17:48
To make a separate decision for each school is not necessary. Enough to make one.
Start with think about which functions will have Your system, what information and to whom it will provide.
Try to paint on paper the roles of the system participants (the principal, parent, teacher, student), what action they can take on the portal, what information the portal provides, what information it allows to enter.
Then try to identify information entities (user, parent, teacher, student, Director, school, class, subject, lesson, lessons, class, assessment, homework etc).
Then go to the description of the individual elements of these entities (evaluation - figure score, the link school, class, student, subject, lesson, date, teacher, date of assessment, teacher comment, etc.)
Consider what data types you use to store information.
Create a chart of the future database.
Consider how Your entities will be linked.
July 2nd 19 at 17:50
Don't use terms from a DBMS table, fields, etc.
When working with Django, part of the functionality of the DBMS used behind the scenes and does not require your intervention
However, the design requires you use 3NF

So you need to check out how to make the CustomUser in Django and how you will expand the management of access rights to limit access директор_может_управлять_только_своей_школой etc

spoiler
class School(models.Model):
 name = models.CharField(max_length=200)
 address = models.CharField(max_length=200)


class MyUser(AbstractBaseUser):
 GROUP_DIRECT = 0
 GROUP_TEACHER = 1
 # ...
 GROUP_PARENT = 8
 GROUP_STUDENT = 9
 GROUP = (
 (GROUP_DIRECT, 'Director'),
 (GROUP_TEACHER, 'Teacher'),
 # ...
 (GROUP_PARENT, 'Parent'),
 (GROUP_STUDENT, 'Student'),
)

 name = models.CharField(max_length=200)
 email = models.EmailField()
 group = models.IntegerField(choices=GROUP)


class Lesson(models.Model):
 name = models.CharField(max_length=200)
 # ...
 school = models.ForeignKey(School)


class Director(MyUser):
 description = models.CharField(max_length=200)
 # ...
 school = models.ForeignKey(School)


class Teacher(MyUser):
 description = models.CharField(max_length=200)
 # ...
 school = models.ForeignKey(School)


class Student(MyUser):
 description = models.CharField(max_length=200)
 # ...
 school = models.ForeignKey(School)


class Parent(MyUser):
 description = models.CharField(max_length=200)
 children = models.ManyToManyField(Student)
 # ...


In principle, there is no need to make the perfect design - you can step to add actors and their functions
Don't get excited.) Don't need a bunch of tables with users to do. All of this can go native groups and native rights(and I think a custom user to do not necessarily ). Did not expect from you such an answer, apparently rushed). - Letha_Kuhn commented on July 2nd 19 at 17:53
: how to do everything in 3NF if useritem differ
As stoenie means to restrict access of the user "Director" to the records of other schools?
How can you not create a custom user, if a student can not be email? - marilyne_Roh commented on July 2nd 19 at 17:56

Find more questions by tags DjangoPostgreSQLPython