I am trying to build back end for college ERP system. The system will be based on LAMP.

These is the scenario:

  • Colleges have 5 branches
  • There are 4-6 classes in each branch.
  • There are 80 students in each class.
  • Each class has almost 6-7 subjects.
  • There are around 250 Faculties.
  • Each of them teaches one or more subjects and prepares a Teaching Plan for each of them.
  • There are two semesters in a year and two test are conducted in each semester.
  • Continuous assessments (CA) are conducted for per subject & per student basis. It comprises of 12 Experiments and 3 Assignment marks.
  • CA leads to the term work marks calculation at the end of semester.

Hence the problem is to record the data regarding attendance, Continuous assessment, Test Marks of each student etc

I have build a sample schema but the problem is that it is not scalable. Because of the following:

  • If a subject is of 40 lectures then it requires almost 80*40=3200 records for recording attendance of a single subject per class; so what about 6 more subjects in each semester.
  • Take a look here Sample College Schema

So my questions are:

  • Which tables should be kept as they are [i.e should be created only once] and which tables should be created dynamically and at what interval? [i.e year-wise/ semester-wise/ any other suggestions]
  • How to make data retrieval optimal?.
  • How to redesign schema so that it scales well.
  • How can I estimate size of each table in my database. Whether the fields which remains null / empty occupy space [whether they are taken into consideration in size calculations]

Thank you in advance for any suggestion or help.

closed as too broad by gnat, user40980, GlenH7, Tulains Córdova, Kilian Foth Dec 12 '14 at 13:21

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 8
    by my calculation you have at most 20,000 student/subject pairs and therefore at most 800,000 attendance records per year. Say you need to keep those records for 12 years (5 years during which the student is potentially still present, 7 year retention once they have left) you have less than 10,000,000 records. A single table on a single server should be able to handle this fine, as long as you have appropriate indices defined. Dynamically creating tables is almost never the right answer. – Jules Dec 9 '14 at 8:40
  • @Jules so my question is that whether the data retrieval will be optimal i.e. response time should be minimum. – geeksal Dec 11 '14 at 4:06
  • Please see the edited question – geeksal Dec 11 '14 at 4:11
  • 3
    @Jules "Dynamically creating tables is almost never the right answer" <--- that should be first sentence uttered by every professor at the start of every class on databases. – GrandmasterB Dec 11 '14 at 4:54
  • @geeksal I don't think you'll have any trouble with performance. Your problem is not one that's large enough to need to worry about scalability, at least not at this stage. Worry about designing the code right, keeping everything well-factored so that it can be easily changed later if you need to, but I doubt you'll have any problems with databse performance. Databases are good at this, it's what they're designed to do. – Jules Dec 11 '14 at 7:53

Initial response

Some preliminary matters:

  • As a practical matter I find it's helpful to throw in date fields for created/modified times; it makes your life much easier when debugging data problems. Pretty much any table that's not a lookup table would benefit from it. If a table is write-once, consider foregoing the modified time column.

  • Be sure that you're not storing passwords in plaintext. It's beyond the scope of this question but I thought I'd take the time to mention it.

That said, I think you're prematurely worrying about things like scalability until you've actually got some data to back up your problems. That said, I do have one easy suggestion.

If a subject is of 40 lectures then it requires almost 80*40=3200 records for recording attendance of a single subject per class; so what about 6 more subjects in each semester.

It's true that you're adding a lot of records but those records so far look to be... 10 bytes each? I'm not going to look up the sizes of the data types but you're not breaking the bank with 10 byte rows. I suspect that while the users always want to have attendance available, it's likely that agreeing on retention policy can help you to cap the growth and allow you to prune attendance records from years past.

Still, you've got room to optimize. Rather than recording attendance for each Student in a one-to-one relationship, I suggest you record attendance by picking the smaller of "attended" or "missed" and record only that. For example, if you assume most students go to lecture you would pick "missed," which means the Attendance table will only have rows for Students who missed lecture. If a Student has a row in the Attendance table he missed lecture, otherwise he was present. Of course in such a case, Attendance should be renamed to something like Absence or something.

Spend more time considering what you might do when your stakeholders want to add more data to your schema. Examples include adding categories or groupings, aliases or secondary names or secondary emails.

Regarding dynamic table generation

Which tables should be kept as they are [i.e should be created only once] and which tables should be created dynamically and at what interval? [i.e year-wise/ semester-wise/ any other suggestions]

This is a premature concern as you don't know what your performance problems are yet. I'd be inclined to look for badly performing tables after release and building up an archiving process depending on your needs.

I don't recommend dynamically creating tables as you'd have to make edits to the database schema on the live host that impact your application, which have the potential to bring down the application if you commit a typo. To answer your question, I recommend archiving data at the row level.

If you need old data to be highly available but infrequently accessed, you could surface it using a separate module of the application, with its own separate database. This database could be populated by an external process that moves rows (during a maintenance window) from the live/active module to the archive module.

If you don't need it to be as highly available you could export rows from the live database (during a maintenance window) to a compressed archive that is properly backed up and can be reviewed on-demand using developer tools.

The benefit with this approach is that your row operations won't take down the site; worst case is that some data is missing for awhile or performance is degraded.

As the lead developer it's up to you to determine what the needs are and act accordingly. Again, I recommend you put this kind of work off until you have a concrete problem.

  • modified fields are useless, you only get the info about the last change (you do not even know which change was it). Other than that, I generally agree. It is also worth mentioning that most DBMS allow for partitioning tables (so "old" records go to slower, cheaper storage) for improving scalability. – SJuan76 Dec 9 '14 at 11:55
  • thanks for the suggestion regarding attendance table. I am also looking for more answer regarding dynamic table generation and scalability of records if i preserve data for around 10 years. – geeksal Dec 11 '14 at 3:56
  • Please see the edited question – geeksal Dec 11 '14 at 4:13
  • @SJuan76: Modified fields are no silver bullet but I still claim they're useful in the right contexts. – phasetwenty Dec 12 '14 at 1:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.